Getting Save File information with SQL

Added to our SQL "toolkit" with the latest round of Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, were two Views and a Table function that helps me get information about Save Files, and their contents.

Prior to these if I wanted to know how many save files I have in my library I would use the Work Object PDM command, WRKOBJPDM:

WRKOBJPDM LIB(MYLIB) OBJTYPE(*FILE) OBJATR(*SAVF)

Which displays:

Work with Objects Using PDM Library . . . . . MYLIB Position to . . . Position to type Opt Object Type Attribute Text __ SIMONSAVF *FILE SAVF __ SIMONSAVF2 *FILE SAVF

Or to list the contents of a save file I would use the Display Save File, command:

DSPSAVF FILE(SIMONSAVF)

Which displays:

Display Saved Objects Library saved . . . . . . . : MYLIB Type Options, press Enter. 5=Display Opt Object Type Attribute Owner _ @BASIC *FILE PF SIMON _ @FULL *FILE PF SIMON _ @NAME *FILE PF SIMON _ @SERVICE *FILE PF SIMON _ @TESTATR *FILE PF SIMON

Neither of these commands offer an option to create an output file so I can easily do things with the information, like make a count of all the Save Files, or check to see which Save Files contain a certain object.

That is until now. Now we have the following:

• SAVE_FILE_INFO:  Save file information View
• SAVE_FILE_OBJECTS:  Information about objects within one Save File Table Function
• SAVE_FILE_OBJECTS:  Information about objects in every Save File View

 

SAVE_FILE_INFO:  Save file information View

If I need to get a list of the save files in my library, or all libraries, or all save files created on a certain day, I can do that with this View.

If this is the first time you are using this View I always encourage you to use the following statement to see which columns are available to use:

SELECT * FROM QSYS2. SAVE_FILE_INFO

I am not going to show the results from that Select statement as there are more columns than would fit the width of this page.

What I will do is give a couple of examples.

Here I want to list all of the save files in my library, MYLIB, with some additional information. This I can do with the following statement:

01 SELECT SAVE_FILE, 02 SAVE_TIMESTAMP, 03 OBJECTS_SAVED, 04 SAVE_COMMAND, 05 DATA_COMPRESSED, 06 SAVE_WHILE_ACTIVE, 07 LIBRARY_NAME 08 FROM QSYS2.SAVE_FILE_INFO 09 WHERE SAVE_FILE_LIBRARY = 'MYLIB'

Return the following columns in my results:

Line 1: Name of the save file. I don't need the name of the library as I only select my library in the WHERE clause, line 8.

Line 2: The time and date when the objects were most recently saved to the save file.

Line 3: The number of saved objects within the save file.

Line 4: The save command used to save the objects.

Line 5: Did the save command use the compression parameter?

Line 6: Was the save performed with "save while active"?

Line 7: The name of the library the objects were saved from.

The results from my two save files are:

SAVE_ OBJECTS SAVE_ DATA_ WHILE_ LIBRARY SAVE_FILE SAVE_TIMESTAMP _SAVED COMMAND COMPRESSED ACTIVE _NAME ---------- ------------------- ------- ------- ---------- ------ ------- SIMONSAVF 2022-02-09 11:25:52 5 SAVOBJ NO *NO MYLIB SIMONSAVF2 2023-06-07 08:32:26 159 SAVOBJ YES *NO MYLIB

The results show that when I use the Save Object command, SAVOBJ, to save the objects in the second save file I did use data compression.

As part of the regular clean up routines I delete save files that are more than three months old. Using this view is going to make it so much easier to make a list of the oldest save files. I would use a statement like this:

01 SELECT SAVE_FILE_LIBRARY,SAVE_FILE,SAVE_TIMESTAMP 02 FROM QSYS2.SAVE_FILE_INFO 03 WHERE DATE(SAVE_TIMESTAMP) < CURRENT_DATE - 3 MONTHS 04 ORDER BY SAVE_TIMESTAMP

Line 1: I am only interested in the save file name, the library it is, and when it was last save to.

Line 3: Here I am selecting every save file that was saved to before three months ago.

Line 4: I am ordering by the save timestamp. This will make the oldest save file be the first result.

A word of warning: Depending upon the number of save files you have in your partition this may take a long time to run.

I can also use the to count the number of save files I have in this partition:

01 SELECT COUNT(*) 02 FROM QSYS2.SAVE_FILE_INFO 03 WHERE SAVE_FILE_LIBRARY NOT LIKE 'Q%'

Line 1: To get a count of save files I use the COUNT scalar function.

Line 3: This is my attempt to exclude any IBM save files, using the assumption that their save files name will start with "Q". I have no idea if that is a safe assumption to make.

The result of the count was:

0001 ----- 2835

 

SAVE_FILE_OBJECTS:  Information about objects within one Save File Table Function

As I now know the save files in my library I can get to know their contents. There are two ways I can get to:

• Via View, this is slow contains the contents of all the save files on this partition. It is going to take some time for it to find and return the information for the save file I care about.
• Via Table function, this is faster as I can pass to the Table function just the save file I am interested in.

In these examples I know which save file I want to know more about, SIMONSAVF, and I know which library it is in, MYLIB, with that information I can use the Table function.

The syntax for the Table function is:

01 SELECT * 02 FROM TABLE(QSYS2.SAVE_FILE_OBJECTS( 03 SAVE_FILE => '< your save file > ', 04 SAVE_FILE_LIBRARY => '< your library > ', 05 OBJECT_NAME_FILTER => '*ALL', -- Default 06 OBJECT_TYPE_FILTER => '*ALL', -- Default 07 DETAILED_INFO => 'ALL'))

For the save file I am using in this example my SQL statement would look like:

01 SELECT LIBRARY_NAME,OBJECT_NAME,OBJECT_TYPE,OBJECT_ATTRIBUTE, 02 MEMBER_NAME,SAVE_TIMESTAMP 03 FROM TABLE(QSYS2.SAVE_FILE_OBJECTS( 04 SAVE_FILE => 'SIMONSAVF', 05 SAVE_FILE_LIBRARY => '*LIBL', 06 OBJECT_TYPE_FILTER => '*FILE', 07 DETAILED_INFO => 'ALL'))

Line 1: The columns I desire are:

• LIBRARY_NAME:  The library the objects were saved from
• OBJECT_NAME:  Name of the saved object
• OBJECT_TYPE:  The type of the saved object
• OBJECT_ATTRIBUTE:  Saved object's attribute
• MEMBER_NAME:  If the saved object is a file this is the its member name
• SAVE_TIMESTAMP:  When the object was saved

Lines 4 – 7: These are the parameters I need to pass to the SAVE_FILE_OBJECTS Table function.

Line 4: I need to pass the save file's name.

Line 5: The library the save file is in, or I can pass "*LIBL".

Line 6: I am only interested in the files in the save file. I only have files in this save file so I could have ignored this parameter if I wanted to.

Line 7: I want all the information about these objects.

When I run the statement I am returned the following results:

OBJECT _ MEMBER LIBRARY OBJECT_NAME _TYPE ATTRIBUTE _NAME SAVE_TIMESTAMP ------- ---------- ------ --------- -------- ------------------- MYLIB @BASIC *FILE PF @BASIC 2022-02-09 11:25:52 MYLIB @FULL *FILE PF @FULL 2022-02-09 11:25:52 MYLIB @NAME *FILE PF @NAME 2022-02-09 11:25:52 MYLIB @SERVICE *FILE PF @SERVICE 2022-02-09 11:25:52 MYLIB @TESTATR *FILE PF @TESTATR 2022-02-09 11:25:52

There are many other columns of information. I do encourage you to run this with SELECT * to see if there are other columns you would consider important.

 

SAVE_FILE_OBJECTS:  Information about objects in every Save File View

As I said above I should not use this View if I want information about a specific save file, as it could take a long time to generate the results.

The best scenario I can think of using this View is to see if I have saved multiple copies of the same object in the multiple save files.

The SQL statement for finding this information would be:

01 SELECT SAVE_FILE_LIBRARY,SAVE_FILE,LIBRARY_NAME,OBJECT_NAME, 02 OBJECT_TYPE,SAVE_TIMESTAMP 03 FROM QSYS2.SAVE_FILE_OBJECTS 04 WHERE LIBRARY_NAME = 'MYLIB' 05 AND OBJECT_NAME = '@NAME' 06 AND OBJECT_TYPE = '*FILE' 07 ORDER BY SAVE_TIMESTAMP DESC

Lines 1 and 2: I am only interested in the following columns:

• Save file and the library it is in
• Object, library it is in, and object type
• Save timestamp

Lines 4 – 6: Here I am selecting the rows I want in my results.

Line 7: I want the results sorts in reverse order by the save timestampo, so the most recent comes first.

This statement took a long time to return the results:

SAVE_ FILE_ SAVE_ LIBRARY OBJECT LIBRARY FILE _NAME _NAME SAVE_TIMESTAMP ======= ========== ======= ===== =================== MYLIB SIMONSAVF2 MYLIB @NAME 2023-06-07 08:32:26 MYLIB SIMONSAVF MYLIB @NAME 2022-02-09 11:25:52

Now I know if I need to restore the most recent version of the object I would restore the object from the SIMONSAVF2.

This took so long I decided if I have need to run this again I would run it in batch.

As the statement to create an output file is so long I did not want to key it directly into a Submit Job command, SBMJOB. I created a CL program with the SBMJOB in it:

01 PGM 02 SBMJOB CMD(RUNSQL SQL('+ 03 CREATE OR REPLACE TABLE U4142SH.OUTFILE AS + 04 (SELECT SAVE_FILE_LIBRARY,SAVE_FILE,LIBRARY_NAME,OBJECT_NAME,+ 05 OBJECT_TYPE,SAVE_TIMESTAMP + 06 FROM QSYS2.SAVE_FILE_OBJECTS + 07 WHERE LIBRARY_NAME = ''MYLIB'' + 08 AND OBJECT_NAME = ''@NAME'' + 09 AND OBJECT_TYPE = ''*FILE'' + 10 ORDER BY SAVE_TIMESTAMP DESC) + 11 WITH DATA + 12 ON REPLACE DELETE ROWS') + 13 COMMIT(*NC)) + 14 JOB(SAVF_VIEW) JOBQ(QPGMR) 15 ENDPGM

I am using the SBMJOB command to submit the RUNSQL command to run in batch.

Line 2 – 14: We are creating a SQL table "on the fly", from the output of a SQL Select statement. I have used the CREATE OR REPLACE TABLE which will replace an existing table, therefore, on line 12, I have to tell the statement what to do with the data in an existing file.

Another way would be to create a source member, in a source file, containing the SQL statements I want to execute:

01 DROP TABLE IF EXISTS MYLIB.OUTFILE ; 02 CREATE OR REPLACE TABLE MYLIB.OUTFILE AS 03 (SELECT SAVE_FILE_LIBRARY,SAVE_FILE,LIBRARY_NAME,OBJECT_NAME, 04 OBJECT_TYPE,SAVE_TIMESTAMP 05 FROM QSYS2.SAVE_FILE_OBJECTS 06 WHERE LIBRARY_NAME = ''MYLIB'' 07 AND OBJECT_NAME = ''@NAME'' 08 AND OBJECT_TYPE = ''*FILE'' 09 ORDER BY SAVE_TIMESTAMP DESC) 10 WITH DATA 11 ON REPLACE DELETE ROWS

Line 1: This time I want to use the DROP TABLE statement to delete any existing table. By using the IF EXISTS I stop the statement from erroring if the file does not exist.

Lines 2 – 11: Statement to create the output table.

I can then execute the statements with the following SBMJOB command:

SBMJOB CMD(RUNSQLSTM SRCFILE(MYLIB/DEVSRC) SRCMBR(TESTSQL) COMMIT(*NC) MARGINS(*SRCFILE)) JOB(SAVF_VIEW) JOBQ(QBATCH)

Both of those will generate the same results, a file called OUTFILE in the library MYLIB.

 

You can learn more about this from the IBM website:

• SAVE_FILE_INFO View
• SAVE_FILE_OBJECTS Table function
• SAVE_FILE_OBJECTS View

 

This article was written for IBM i 7.5 TR2 and 7.4 TR8.