FUNCTIONS

Support email

www.MidrangeNews.com

Contact Us

iQueryLogo

SQL iQuery™ UDF's and Procedures

The SQL iQuery product includes the following functions and ("stored") procedures.

The advantage of these UDFs is that they are run by SQL regardless of environment (STRSQL, embedded SQL, ACS Run SQL Scripts, or of course SQL iQuery.

SQL iQuery Functions have adpoted the IBM-standard for SQL functions when it comes to object parameters. This means that when an library (schema) and object are specified, they are typically specified as OBJ,lib,type vs the IBM API standard of OBJ, LIB, TYPE. For example, to use the iQuery.MBRLIST() function, specify library, file, member. e.g., select * from table(iQuery.mbrlist('COZTOOLS','QCPYSRC','RTV*')) ml
this creates a list of members that begin with 'RTV' from the Source File QCPYSRC in the COZTOOLS library.

Visit the SQL iQuery Product Information Page

An example of the MBRLIST UDF is featured at this link.

Note: You can scroll through this table using your mouse roller/scroll wheel. Likewise, you can sort it by any column by clicking on the title of the column. Oh, and FYI, this HTML Scrolling Table is included in SQL iQuery at no additional cost.

Cozzi SQL iQuery (2COZ-IQ4) Function List
iQuery UDF, UDTF, and Procedures Listing
Specific
Name
Function
Type
DESCRIPTION
93 records retrieved.
CONVERTDT Function UDF to return a true SQL/DB2 "date" value from a date that is in textual (character) format. This function supports both numeric and character input values so it may be listed twice. It is similar to our CVTDATE() UDF except CONVERT_DATE's source code is included with SQL iQuery, and if compiled by the end-user may be placed into any library. The data to be convert to date may be specified in any of several formats. Specify the data to be converted to a date as the first parameter, its format as the 2nd parameter (the default is YMD). An input value of CONVERT_DATE('180731') returns a true date value of D'2018-07-31'. The input value is normally a field from a legacy database that contains date information but is stored as numeric or character data-types. This function is also useful on the ORDER BY clause of a SELECT statement: select custnbr, compyname, iQuery.convert_date('180731','ymd') from mydata.salesHist;
CONVERTDTD Function UDF to return a true SQL/DB2 "date" value from a date that is in textual (character) format. This function supports both numeric and character input values so it may be listed twice. It is similar to our CVTDATE() UDF except CONVERT_DATE's source code is included with SQL iQuery, and if compiled by the end-user may be placed into any library. The data to be convert to date may be specified in any of several formats. Specify the data to be converted to a date as the first parameter, its format as the 2nd parameter (the default is YMD). An input value of CONVERT_DATE('180731') returns a true date value of D'2018-07-31'. The input value is normally a field from a legacy database that contains date information but is stored as numeric or character data-types. This function is also useful on the ORDER BY clause of a SELECT statement: select custnbr, compyname, iQuery.convert_date('180731','ymd') from mydata.salesHist;
CSV_UDTF Table Function UDTF to read a CSV (comma separated values file) from the IFS. Use CSV_VAL(), CSV_INT(), CSV_DEC(), CSV_DATE() and CSV_BIGDEC() to retrieve the data of each column and convert it from CSV to database columns.
CSV_VAL_COLID Function UDF to read a CSV value from a CSV file. CSV_VAL( data, relative-column-number )
CSV_VAL_COLNAME Function UDF to read a CSV value from a CSV file. CSV_VAL( data, 'column-name' )
DATEDIFF Function
DATEDUR Table Function
DECDATE_CURDATE Function
DECDATE_CURDATE_FORMATOVERRIDE Function
DECDATE_DATE_FMT Function
DECDATE_USERDATE Function
DECEDIT_DEFAULT Function
DECEDIT_RETURNLEN Function
DTSDIFF Function
GET_MONTH_END Function
GET_MONTH_START Function
IQ_ADDLIBL Procedure
IQ_ADDLIBLE Procedure
IQ_CACHEBATTERY Table Function UDTF that retrieves the IBM i system cache battery status. Returns a table with the current status of all cache batteries. the same information: e.g., select * from table( iQuery.cacheBattery() ) cb;
IQ_CHECKOBJECT Function UDF to check if an object exits. Specify the library name, object name and optional object type. Syntax: CHKOBJ('LIBRARY', 'OBJECT-NAME', [, 'object-type' default is *FILE ]) The UDF returns '1' if the object exists otherwise it returns '0'. e.g., VALUES iQuery.chkobj('QIWS','QCUSTCDT') INTO :exists;
IQ_CHGCURLIB Procedure
IQ_CHGLIBL Procedure
IQ_CHKLIBL Function UDF that returns 1 if the specified library is on the library list otherwise it returns 0 (same as the CHKLIBLE() UDF) e.g., values iQuery.chklibl('IQUERY') into <host-variable>;
IQ_CHKLIBLE Function UDF that returns 1 if the specified library is on the library list otherwise it returns 0 (same as the CHKLIBL() UDF) e.g., values iQuery.chklible('IQUERY') into <host-variable>;
IQ_CPUCOUNT Function UDF to return the number of active cores on this system (all partitions. e.g., VALUES cpuCount() INTO <host-variable>;
IQ_CPYTOPDF Procedure UDF that copies an existing SPOOL file from an Output Queue to the IFS as a PDF file. The interface uses a Workstation customization object to perform the translation. The parameters are: Parm 1: PDFFile - The name of the PDF file being created. Parm 2: SPOOL - The SPOOL file name, Parm 3: SPLNBR - The SPOOL file number or 0 for *ONLY or -1 for *LAST. default is 0. Parm 4: Job. Full qualified job name DFT is QSYS2.JOB_NAME, e.g., call iQuery.cpytopdf('/home/cozzi/dsplibl.pdf','REPORT');
IQ_CURLIB Function UDF that returns the job's current library. e.g., values iQuery.curlib() into <host-variable>;
IQ_CVTDATE_CHAR Function UDF to return a true SQL/DB2 "date" value from a date that is in textual (character) format. This function supports both numeric and character input values so it may be listed twice. The date may be in any of several formats. Specify the date to be converted as the first parameter, its format as the 2nd parameter (the default is YMD). An input value of CVTDATE('180731') returns a true date value of (it will return '2018-07-31'). The input value is normally a field from a legacy database field that contains date information but are not actually stored as DATE data-type fields. This function is also useful on the ORDER BY clause of a SELECT statement: select custnbr, compyname, iQuery.cvtdate('180731','ymd') from mydata.salesHist;
IQ_CVTDATE_DEC Function UDF to return a true SQL/DB2 "date" value from a date that is in decimal (numeric) format. This function supports both numeric and character input values so it may be listed twice. The date may be in any of several formats. Specify the date to be converted as the first parameter, its format as the 2nd parameter (the default is YMD). An input value of CVTDATE(180731) returns a true date value of (it will return '2018-07-31'). The input value is normally a field from a legacy database field that contains date information but are not actually stored as DATE data-type fields. This function is also useful on the ORDER BY clause of a SELECT statement: select custnbr, compyname, iQuery.cvtdate(180731,'ymd') from mydata.salesHist;
IQ_DMG_OBJLIST Table Function UDTF that returns a resultset (table) with a list of objects that are damaged or partially damaged. This returns the list of objects that are damaged. This UDTF returns the list of all damanged objects the specified library or all libraries if no library was specified: e.g., select * from table( iQuery.dmp_objlist('MYUSERLIB') ) dmg;
IQ_FROMHEX Function UDF to convert a set of hexadecimal character pairs into single-characters. e.g., VALUES fromHex(X'F1F2F3') into <host-variable> returns '123'
IQ_FTPDIR Procedure Procedure that sends the contents (all files) of an IFS directory to another system (any system) using FTP. This sends the files in the /HOME/COZZI folder to the remote CHICAGO server. It uses User ID COZZI and sends the password ROSEBUD to log in using FTP: e.g., call iQuery.ftpDir('CHICAGO','COZZI','ROSEBUD','/HOME/COZZI');
IQ_GET_ENVVAR_VALUE Function UDF to retrieve an Environment Variable's value. The GETENV() UDF is one of our Open Source UDFs. The syntax is: GETENV('<environment-variable>'). e.g., VALUES getenv('JAVA_HOME') INTO <host-variable>;
IQ_GETJOBNAMECOMPONENT Function UDF that returns the portion of the job name requested. The user may request the JOBNAME, JOBUSER, JOBNBR, or INTID The INTID returns the 16-byte internal Job Identifier that may then be sent to APIs or other interfaces to more quickly locate the job. e.g., values iQuery.job('jobnbr') into <host-variable>;
IQ_GETOBJSYSNAME Function UDF that returns the short, 10-character System Name for the object. The first parameter may be either the library name for the object specified in parameter 2, or a library name that is to be shortened itself (i.e., a long schema name) In this case, parameter 2 must contain *LIB or *SCHEMA. To convert a file (table) name without qualifying it (i.e., using *LIBL) then specify that file name as parameter 1 and either omit parameter 2 or specified *FILE for parameter 2. Please note that conversion can only be performed on Library (schema) names or File (table) names. Other Long Names such as Global SQL Variables cannot have their system object name retrieved. e.g., values iQuery.getobjSysName('MyLongSchemaName', 'This_is_a_long_name','*VAR') into <host-variable>;'
IQ_GETSHORTNAME Function UDF that returns the short, 10-character System Name for the object. The first parameter may be either the library name for the object specified in parameter 2, or a library name that is to be shortened. In this case, parameter 2 must contain *LIB or *SCHEMA. To convert a file (table) name without qualifying it (i.e., using *LIBL) then specify that file name as parameter 1 and either omit parameter 2 or specified *FILE for parameter 2. Please note that conversion can only be performed on Library (schema) names or File (table) names. Other Long Names such as Global SQL Variables cannot have their system object name retrieved using this function. For those use the GETOBJSYSNAME() function instead. e.g., values iQuery.getShortName('MyLongSchemaName','This_file_name_is_long') into <host-variable>;'; or: values iQuery.getShortName('MyLongSchemaName','*LIB') into <host-variable>;
IQ_GETSYSNAME Function
IQ_HTTP_TAGENCODE Function UDF to return an HTML Tag-safe encoded character set. unlike URLENCODE() TAGENCODE() only escapes the 5 base characters used by HTML. e.g., select itemnbr iQuery.tagencode(itemDesc) from mydata.salesHist where region = 'CHICAGO';
IQ_HTTP_URLENCODE Function UDF to return the encoded character set that can be safely used in HTML URL links. Unlike the IBM function, this routine uses C/C++ to do the encoding instead of Java. So no java runtime needs to be started. e.g., select itemdesc, iQuery.urlencode(itemnbr) from mydata.saleshist where region = 'CHICAGO';
IQ_IFSCOPY Function UDF that copies an IFS file to another IFS file. e.g., values iQuery.ifsCOPY('/home/cozzi/fromfile.txt','/home/backup/tofile.txt','R') into <host-variable>;
IQ_IFSCOPY_PROC Procedure Procedure that copies an IFS file to another IFS file. e.g., call iQuery.ifsCOPY('/home/cozzi/fromfile.txt', '/home/backup/tofile.txt','R');
IQ_IFSDELETE Function UDF that deletes an IFS file. e.g., values iQuery.ifsDelete('/home/cozzi/fromfile.txt');
IQ_IFSDELETE_PROC Procedure Procedure that deletes an IFS file. e.g., call iQuery.ifsDelete('/home/cozzi/fromfile.txt');
IQ_IFSDIR Table Function UDTF to view the contents of an IFS directory. The names and attributes (file size, creation date, type) are listed. use the ENTTYPE column to check for STMF (files) or DIR (directory names). The 2nd parameter, if anything except the default or 0 performs a recursive scan of the directory. This results in the contents of each subfolder within the one specified to also be listed. e.g., select * from table( iQuery.ifsDir('/home/cozzi',1) ) dir;
IQ_IFSEXISTS Function UDF that check if the IFS file exists. e.g., values iQuery.ifsExists('/home/cozzi/fromfile.txt'); into <host_variable>;
IQ_IFSFILE Table Function UDTF to view the contents of an IFS text file. e.g., select * from table( iQuery.ifsFile('/home/cozzi/fromfile.txt') ) ifs;
IQ_IFSRMTCOPY Procedure Procedure that copies an IFS file to another IBM i system's IFS. This copies the files named SALES*.csv in my home folder, to our remote CHICAGO server: e.g., call iQuery.ifsCOPY('/home/cozzi/sales*.csv','CHICAGO','*YES');
IQ_JOB_ATTR Table Function UDTF that displays a one-row table containing the basic job information. See IBM's JOB_INFO() UDTF for other information. The JOB_ATTR UDTF returns job name, number, start date, job description, mode, log CL program, and most properties returned by the QUSRJOBI API for format JOBI0400, however it also includes the Job Date from JOBI0300. UDTF information for "this" job to be extracted more easily: e.g., select * from table( iQuery.job_attr() ) joba;
IQ_JOB_DATE Function UDF that returns the job's runtime job date. This can be different from the SQL CURRENT_DATE and from the System date. It is the value change via the CHGJOB DATE(xxxxx) command. (it is the same as the iQuery.JOBDATE() UDF) e.g., values iQuery.job_date() into <host-variable>;
IQ_JOBDATE Function UDF that returns the job's runtime job date. This can be different from the SQL CURRENT_DATE and from the System date. It is the value change via the CHGJOB DATE(xxxxx) command. (it is the same as the iQuery.JOB_DATE() UDF) e.g., values iQuery.jobdate() into <host-variable>;
IQ_LIBLIST Table Function UDTF that returns a resultset (table) with a list of libraries on the library list of the job running this UDTF. e.g., select * from table( iQuery.lib_list() ) ll;
IQ_MBRLIST Table Function UDTF that returns a resultset (table) with a list of member names within the designated file. Parameter 1 is the library name. Parameter 2 is the file name or generic file name (SQL or IBM i CL generic supported) Parameter 3 is the member name filter. Parameter 4 is the member text description filter. All parameters are optional, but using only iQuery.MbrList() would be a lengthy process. The resultSet from this UDTF is designed to closely respemble the DSPFD TYPE(*MBRLIST) format: e.g., select * FROM table(iquery.MBRLIST('lib', <'file'>,<'mbr'>,<'text'>)) ML; Note that although the System Name on which the member was added is not included in the IBM QSYS2.OBJECT_STATISTICS() UDTF resultSet until IBM i V7R3, we do return it on v7r2 using our own RTVOBJD UDTF via a lateral join.
IQ_MCHINFO Table Function UDTF that displays a one-row table containing the IBM i machine type and model number, along with the processor group, feature code, serial number, system name and IBM i version/release level in two different formats: (1) VxRyMz and (2) v.r where v is the version and r is the release level. RUNiQRY *MCHINFO otherwise: e.g., select * from table( iQuery.mchinfo() ) mi;
IQ_MD5_BINARY_HASH Function UDF to return the MD5 hash in "binary" format (16-byte form). Unlike HASH_MD5(), BINARY_MD5() returns a result that normally includes non-printable characters. The result is a 16-character value. This implementation converts the input data to ASCII (CCSID 819) before performing the HASH algorithm. This provides a cross-platform result; one that can be compared to the MD5 hash results on other platforms. A frequent use for this hash is for storing so call "encrypted" passwords. e.g., update mydata.userData set USRPWD = binary_md5(:usrpwd) where userID = :usrprf;
IQ_MD5_HASH Function UDF to return the MD5 hash in 2-byte form (ake hex). This 32-byte value can be used to store data in a visually encrypted form but also "text safe" meaning no binary or non-printable characters are used in the result. This implementation converts the input data to ASCII (CCSID 819) before performing the HASH algorithm. This provides a cross-platform result; one that can be compared to the MD5 hash results on other platforms. A frequent use for this hash is for storing so call "encrypted" passwords. e.g., select 1 FROM mydata.userData where hash_md5(:usrPwd) = savedPwd;
IQ_OBJDATE_TO_DATE_TIME Function UDF that converts a 13-character date/time into a true TIMESTAMP result value. This is primarily for internal use only. However access to it shall not be changed in the future.
IQ_OBJEXPORTS Table Function UDTF that returns a resultset (table) with the specified program's or service program's expored procedure namest. This returns the list of procedures that have been exported from service program or program: e.g., select * from table( iQuery.obj_exports('IQUERY', 'COZSQLLIB')) ex;
IQ_OBJLIST Table Function UDTF that returns a resultset (table) with a list of objects in the specified library. This returns the list of objects (programs in this example) that exist in library IQUERY. The same object and library and type parameters that are valid for the QGYOLOBJ API are valid for the OBJ_LIST() UDTF: e.g., select * from table( iQuery.obj_list('IQUERY','*ALL', '*PGM') ) ol;
IQ_OBJSTRUCT Table Function UDTF that returns a resultset (table) with the specified program or service program composition. That is the *MODULE and *SRVPGM objects that make up the object are listed. This returns the list of *MODULE and *SRVPGM objects that are used by the RUNIQRY program in the IQUERY library. The component objects include the creation date, creation source and source last change info. e.g., select * from table( iQuery.obj_struct('IQUERY','RUNIQRY', '*PGM')) obj;
IQ_OSVER Function UDF to retrieve the IBM i version/release level as an integer. This returns the integer value for the opeating system level 730: e.g., VALUES osVER() INTO <host-variable>;
IQ_OSVRM Function UDF to retrieve the IBM i version/release in VxRyMz format. This returns a text string V7R3M0 (or the current VRM) of the operating system: e.g., VALUES osVRM() INTO <host-variable>;
IQ_PRINTPDF Function UDF that copies a PDF from the IFS to a user-specified Output Queue. e.g., select iquery.printpdf('/home/iquery/info.pdf'); See the SQL iQuery Stored Procedure named PRINTPDF for more info.
IQ_PRINTPDF_PROC Procedure Stored Procedure that copies a PDF from the IFS to a user-specified Output Queue. Parm 1: IFS PDF file name (qualified), Parm 2: OUTQ (*JOB, qualified output queue name) DFT '*JOB', Parm 3: HOLD (*YES, *NO) - Hold On output queue DFT '*NO', Parm 4: SAVE (*YES, *NO) - Save after printer DFT '*NO', Parm 5: COPIES (number of copies) DFT 1 e.g., call iQuery.printPDF('/home/iquery/helloworld.pdf','COZZI') This calls the PRINTPDF to print the PDF file to the OUTQ(COZZI).
IQ_RECURSIVECOPY_IFS Procedure
IQ_RMVLIBL Procedure
IQ_RMVLIBLE Procedure
IQ_RTVCMDD Table Function UDTF that returns a one-row resultset with the specified command's creation description. All SQL iQuery UDTFs follow the IBM parameter syntax when a qualified file or object is specified, which is LIBRARY, Object. The only exceptions are the DTAARA() and USRSPC() UDTFs that use qualified object names as "library/object" (one parameter instead of 2). This returns the CL command's description including most of the data supplied on the CRTCMD command when the command was created. e.g., select * from table( iQuery.rtvcmdd('IQUERY', 'RUNIQRY')) cd;
IQ_RTVDTAARA Function UDF to retrieve all or part of a data area. DTAARA('<qualified-data-area-name>' [, start-pos ] [, length ]) This returns position 5 thru 12 of data area MYDATAAREA: e.g., VALUES dtaara('QGPL/MYDATAAREA',5,8) INTO <host-variable>;
IQ_RTVDTAARAEX Function UDF to retrieve all or part of a data area. DTAARA('<library-name>','data-area-name', [, start-pos ] [, length ]) This returns position 5 thru 12 of data area MYDATAAREA in QGPL: e.g., VALUES dtaara('QGPL','MYDATAAREA',5,8) INTO <host-variable>;
IQ_RTVJOBA Table Function
IQ_RTVJOBD Table Function UDTF that returns a one-row resultset with the specified Job Description information. Use this as a LATERAL join with an object list function such as iQuery's OBJ_LIST() or IBM's OBJECT_STATISTICS() to create a list of Job Description names and their descriptions. e.g., select * from table( iQuery.rtvjobd('QGPL','QBACH')) jobD;
IQ_RTVLASTSPLFID Table Function UDTF that returns a resultSet (table) with information that identifies the SPOOL file created by the job running this UDTF. Note that due to an issue in SQL and the QSPRILSP API, this UDTF currently returns no data. There are no parameters. e.g., select * from table( iQuery.rtvlastSplf() ) splfInfo;
IQ_RTVLIBD Table Function
IQ_RTVMBRD Table Function UDTF that returns a one-row resultset with the specified file's member description. All SQL iQuery UDTFs follow the IBM parameter syntax when a qualified file or object is specified, which is LIBRARY, Object. The only exceptions are the DTAARA() and USRSPC() UDTFs that use qualified object names as "library/object" (one parameter instead of 2). This returns the following: file name, library, member name, source (SEU) type, an "Is Source" flag, file attribute, member text, creation date/timestamp and last changed date/timestamp: e.g., select * from table( iQuery.rtvmbrd('COZTEST','QRPGLESRC', 'ORDENTRY')) md;
IQ_RTVNETA Function UDF that returns the user-specified network attribute. Any network attribute from the QWCRNETA API may be returned. Use the same Network Attribute name as specified in the QWCRNETA API documentation in the IBM Knowledge center. The most frequently used network attributes include: LCLLOCNAME (local location name), LCLCPNAME (Local control point), PNDSYSNAME (pending system name), and SYSNAME (System name). e.g., values iQuery.RTVNETA('LCLCPNAME') into <host-variable>;
IQ_RTVOBJD Table Function UDTF that returns one row with the object's description. We return most of the info from the QUSROBJD API with format OBJD0300. While there is the IBM-supplied QSYS2.OBJECT_STATISTICS() UDTF to perform a similar task, this one has all the info on IBM i V7R2 and later. e.g., select * from table( iQuery.rtvobjd('IQUERY','RUNIQRY', '*cmd')) od;
IQ_RTVUSRSPACE Function UDF to retrieve all or part of a user space (*USRSPC) object. USRSPC('<qualified-user-space-name>' [, start-pos ] [, length ]) This returns position 37 thru 86 of the user space named OBJLIST in library COZTOOLS: e.g., VALUES usrspc('COZTOOLS/OBJLIST',37,50) INTO <host-variable>;
IQ_RTVUSRSPACEEX Function UDF to retrieve all or part of a user space (*USRSPC) object. USRSPC('<library-name>','<user-space-name>' [, start-pos ] [, length ]) This returns position 37 thru 86 of the user space named OBJLIST in library COZTOOLS: e.g., VALUES usrspc('COZTOOLS','OBJLIST',37,50) INTO <host-variable>;
IQ_SRLNBR Function UDF to return the system serial number. e.g., VALUES SRLNBR() INTO <host-variable>;
IQ_SYSNAME Function UDF to return the system name. e.g., VALUES SYSNAME() INTO <host-variable>;
IQ_VER Function UDF to retrieve the SQL iQuery version as an integer. This returns the integer value for the SQL iQuery version such as 460: e.g., VALUES iqVER() INTO <host-variable>;
IQ_VRM Function UDF to retrieve the SQL iQuery version in VxRyMz format. this returns a text string V4R6M0 (or the current VRM) for SQL iQuery: e.g., VALUES iqVRM() INTO <host-variable>;
KEYFLD Table Function
LICPGM Function
LICPGM_PGMID_ALPHA_RELLVL Function
OVRDBF_FILE_FILE_MBR Procedure
OVRDBF_FILE_MBR Procedure
OVRDBF_MBR Procedure
OVRDBFEX_MBR Procedure
PRIMARYKEY Table Function
RIGHTADJUST Function
TIMEDIFF Function
TS_FMT Function

Check out SQL iQuery for yourself. Download our free trial today.

Copyright © 2014 Cozzi Productions, Inc. All Rights Reserved.