SQL Query File™ UDF's and Procedures
The SQL Query File product includes the following functions and (stored) procedures.
The advantage of these UDFs is that they may be run by SQL itself. This is important when the SQL Server is running and your actual SQL statements are run in that server job. These UDFs allow you to perform conventional tasks, such as modifying your library list, and have those changes routed to the SQL Server job. Other functions, such as SYSNAME() allow you to retrieve important information that may benefit the results of an SQL statement.
An example of the MBRLIST UDF is featured at this link.
Click on the name to view the information for the UDF or procedure.
|ADDLIBLE||Add one or more libraries to the User portion of the library list.|
CALL QUSRSYS.ADDLIBLE( 'COZTOOLS COZQRYF QGPL QTEMP');
|Result:||Add each of the library names specified (separated by a comma and/or a blank) to the library list. It does this by first checking the current library list for each library name. If a library already exists on the library list, that library name is discarded and the procedure attempts to add the other names to the library list.|
|RMVLIBLE||Removes one or more libraries from the User portion of the library list.|
CALL QUSRSYS.RMVLIBLE( 'QGPL QTEMP');
|Result:||QGPL and QTEMP are removed from the library list. If a library does not appear on the library list, it is not removed, and no message is issued.|
|CHKLIBLE||Search the library for the given library name and returns 1 if found, otherwise it returns 0.|
SELECT CASE WHEN CHKLIBL('COZQRYF')=1 THEN 'FOUND' ELSE 'NOT FOUND' end FROM sysdummy1;
|Result:||The result of the SELECT is 'FOUND' or 'NOT FOUND' depending on whether or not COZQRYF library is on the library list.|
|Syntax 2:||SELECT CHKLIBL('COZQRYF') INTO :Found FROM sysDummy1;|
|Result:||The SELECT statement returns 1 if the library is found.|
|CHGCURLIB||Sets the Job's CURRENT LIBRARY to the library spcified.|
|Result:||Changes the current library to QGPL.|
|CHGLIBL||Changes the job's library list to the list of library names specified.|
CALL QUSRSYS.CHGLIBL( 'COZTOOLS COZQRYF QGPL QTEMP');
|Result:||The user portion of the library list is changed to the list of library names specified. NOTE: If a library already occurs on the System Portion of the library list, or any library specified does not exist then the procedure will fail.|
|ifsCOPY||Copy an IFS file to another IFS file. This is a function or a procedure.|
CALL QUSRSYS.IFSCOPY( '/home/sales.csv','/home/test/sales.csv')
SELECT ifsCopy(entFullName,'/saveloc/'||rtrim(entName) FROM TABLE( ifsDir('/home/sales') ) SF WHERE entname like '%.pdf' and enttype = 'STMF'
|Result:||Performs a copy of the designated file to the target location. When the function form is used on a SELECT, it can copy a series of files to a target provided the target is different from the original file. In the example above, we utilize the ifsDir() to generate a list of files contained in the /home/sales folder and copy those files to the /saveloc folder.|
|SYSNAME||Returns an 8-position character value containing the system name of the system on which the SQL statement is run.|
|Syntax:||SELECT SYSNAME() INTO :sysName FROM sysDummy1;|
|Result:||Retrieves the System name and stores it into the SYSNAME host variable.|
|SRLNBR||Returns an 8-position character value containing the system serial number for the system on which the SQL statement is run.|
|Syntax:||SELECT SRLNBR() INTO :srlnbr FROM sysDummy1;|
|Result:||Retrieves the System Serial Number and stores it into the SRLNBR host variable.||HASH_MD5
|Returns a 32-byte MD5 digest ("hash") for the input string (in the case of HASH_MD5) or a 16-byte VARBINARY value (in the case of BINARY_MD5). The input value is automatically converted to ASCII CCSID(1208) by SQL and then the MD5 hash is calculated. This provides the same MD5 hash resulting from this DB2 for i SQL UDF as you would get from a PC-ASCII MD5 routine. In addition. The two functions are identical except in the return values. NOTE: Trailing blanks are trimmed off the input value.|
SELECT hash_md5('abcdefghijlkmnop') into :md5Hash from sysdummy1
|Result:||Calculates the 16-byte MD5 hash for the input string by converting the input string to ASCII. Then the 16-byte MD5 hash is expanded (converted to hex) and the 32-byte text value is returned to the SQL statement. If you prefer the raw 16-byte MD5 hash, use the BINARY_MD5() function instead.|
|IFSFILE||This UDTF (user-defined Table function) returns the content of an IFS text file.|
SELECT * from TABLE( ifsfile('notes.txt') ) X
|Result:||Each line (row) of NOTES.TXT in the current IFS directory is retrieved. The resultset contains one column (field), named TEXTDATA that is returned as VARCHAR(2048). In Your own RPG applications, you can retrieve the TEXTDATA value into a host variable that is a fixed-length character variable, or a variable length VARYING field. You only need it to be defined as long as you know is necessary--it does not need to be defined with a 2k length unless you expect 2048-byte text lines from your IFS text file. Only plain ASCII text files may be specified. Data in the IFS file is converted into the CCSID of the job running the UDTF when it is read. A fully-qualified IFS file and path may be specified, or users may enter a simple name, which looks into the job's current IFS folder. Use WRKLNK to determine the current IFS folder location or specify the fully-qualified IFS file name.|
|MBRLIST||This Table Function returns the list of Members for the specified file. The data fields (columns) returned are the same as those from DSPFD *MBRLIST|
|Syntax:||SELECT * FROM TABLE( mbrlist('QRPGLESRC','QGPL') ) ML|
|Result:||A rowset containing all the member names and information for the file QRPGLESRC in library QGPL.|
|SELECT MLNAME,MLMTXT,MLSEU FROM TABLE( mbrlist('QRPGLESRC','QGPL') ) ML where MLNAME LIKE '%DATE%'|
|A rowset containing member name, text, and SEU type for each member in QRPGLESRC in library QGPL whose name contains the phrase 'DATE' anywhere in the name.|
|OVRDBF||Overrides a database file to a specified member.|
CALL QUSRSYS.OVRDBF( [[file,] toschema,] tofile, tombr);
CALL QUSRSYS.OVRDBF( toschema, file, tombr);
CALL QUSRSYS.OVRDBF( file, tombr);
|All parameters must be enclosed in quotes or stored in host variables.
If the 'file' parameter is not specified
then only 3 parameters are passed to QUSRSYS.OVRDBF, otherwise 4 parameters are passed.
[file] is the optional file name normally specified on the FILE parameter of OVRDBF. If it is unspecfied, the 'tofile' value is used for this value. In this context, only 3 parameters are passed to the procedure.
['schema'] is the optional library name normally specified as the library name on the TOFILE parameter of the OVRDBF command. This parameter may be omitted, blank, a library name, or *LIBL.
'tofile' is the file name that the 'file' is being overridden to. Normally this file name appears on the file portion of the TOFILE parameter of the OVRDBF command.
'tombr' is the name of the member to be overriden to (processed by upcoming SQL statements. This is the same value normally specified on the MBR parameter of the OVRDBF command.
|Overrides "file" to "tofile" in "schema" library, to member "tombr".
Note that QUSRSYS.OVRDBF includes the OVRSCOPE(*JOB) parameter. To remove the override,
run the DLTOVR file LVL(*JOB) command.
This overrides files QCUSTCDT to file QCUSTCDT in QIWS library, and points at member TESTMBR. When the following SQL statement is run, member TESTMBR is processed.
RUNSQLF SQL('SELECT * FROM QCUSTCDT') OUTPUT(*PRINT)
This overrides files QCUSTCDT to file QCUSTCDT in QIWS library, and points at member TESTMBR2. When the following SQL statement is run, member TESTMBR2 is processed.
RUNSQLF SQL('SELECT * FROM QCUSTCDT') OUTPUT(*PRINT)
Check out SQL Query File for yourself. Download our free trial today.