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.

SQL iQuery (2COZ-IQ5) Function List
iQuery UDF, UDTF, and Procedures Listing
Function
Name
Specific
Name
Function
Type
DESCRIPTION
125 records retrieved.
ADDLIBL IQ_ADDLIBL Procedure
ADDLIBLE IQ_ADDLIBLE Procedure Procedure that adds the separated library names to the user-portion of the library list. e.g., call iQuery.AddLiblE('IQUERY COZTOOLS RPGOPEN');
BASE64_DECODE IQ_BASE64_DECODE Function UDF to decode a base64 encoded dataset. The end result may need to be converted to EBCDIC as base64 is intended for ASCII (UTF-8) data. e.g., values iQuery.base64_decode( :fromWeb ) into :myData;
BASE64_DECODE IQ_BASE64_DECODE_CLOB Function
CACHEBATTERY 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;
CHARTODEC IQ_DE_EDIT Function UDF to convert textual numeric values with edit symbols to decimal. For example, text value of '$12,465.72CR' cannot be converted with standard SQL interfaces. the charToDec() UDF will convert this value to -12465.72 correctly. Syntax: charToDec('numeric string' [ , 'optional 3-char edits']) The 2nd parameter is optional and may conain the Currency Symbol, Thousands Separator, and Decimal point notation symbol (in that order).
CHGCURLIB IQ_CHGCURLIB Function UDF that changes the current library to the specified library. There is also a procedure version of this function, it has the same name. To run this user defined function: e.g., values iQuery.CHGCURLIB('QGPL');
CHGCURLIB IQ_CHGCURLIB_PROC Procedure Procedure that sets the current library to the specified library. There is also a user-defined function for this stored procedure, it has the same name. To call the procedure: e.g., call iQuery.CHGCURLIB('QGPL');
CHGLIBL IQ_CHGLIBL Procedure Procedure that sets the user portion of the job's library list to the library names specified. the user-portion of the library list. e.g., call iQuery.CHGLIBL('IQUERY COZTOOLS QGPL CGILIB QTEMP MYDATA');
CHKEXISTS IQ_EXISTS_CHK_IFS Function UDF that checks if the specified IFS file exists. The return value is 1 if the file exists, 0 if the file does not exist. e.g., values iQuery.ifsExists('/home/cozzi/fromfile.txt') into <host_variable>;
CHKLIBL 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>;
CHKLIBLE 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>;
CHKOBJ 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;
CONVERT_DATE IQ_CVTDATE_CHAR Function
CONVERT_DATE 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. 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;
CPUCOUNT IQ_CPUCOUNT Function UDF to return the number of active cores on this system (all partitions. e.g., VALUES cpuCount() INTO <host-variable>;
CPYTOPDF 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');
CRTUSRSPC IQ_CRTUSRSPACE Procedure
CSV 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_BIGDEC CSV_BIGDEC_COLID Function UDF to read a CSV value as a big packed decimal(63,15) value from a CSV file. CSV_BIGDEC( data, relative-column-number )
CSV_BIGDEC CSV_BIGDEC_COLNAME Function UDF to read a CSV value as a big packed decimal(63,15) value from a CSV file. CSV_BIGDEC( data, 'column-name' )
CSV_CHAR CSV_CHAR_COLID Function UDF to read a CSV value as a vachar(2048) value from a CSV file. CSV_CHAR( data, relative-column-number )
CSV_CHAR CSV_CHAR_COLNAME Function UDF to read a CSV value as a vachar(2048) value from a CSV file. CSV_CHAR( data, 'column-name' )
CSV_COUNT CSV_COLUMN_COUNT Function UDF to return a count of the number of columns in the CSV file. it does this by receiving the data from the CSV() UDTF (which is named DATA) and parsing it to return the column count. SELECT CSV_COUNT( data ) FROM table(iquery.csv('/home/sales.csv')) ifs;
CSV_DATE CSV_VAL_DATE_COLID Function UDF to read a CSV value as a date from a CSV file. CSV_DATE( data, relative-column-number, 'input-csv-date-format' )
CSV_DATE CSV_VAL_DATE_COLNAME Function UDF to read a CSV value as a date from a CSV file. CSV_DATE( data, 'column-name', 'input-csv-date-format' )
CSV_DEC CSV_VAL_DEC34_COLID Function UDF to read a CSV value as a packed decimal value from a CSV file. CSV_DEC( data, relative-column-number )
CSV_DEC CSV_VAL_DEC34_COLNAME Function UDF to read a CSV value as a packed decimal value from a CSV file. CSV_DEC( data, 'column-name' )
CSV_INT CSV_VAL_INT_COLID Function UDF to read a CSV value as an integer from a CSV file. CSV_INT( data, relative-column-number )
CSV_INT CSV_VAL_INT_COLNAME Function UDF to read a CSV value as an integer value from a CSV file. CSV_INT( data, 'column-name' )
CSV_VAL CSV_VAL_COLID Function UDF to read a CSV value from a CSV file. CSV_VAL( data, relative-column-number )
CSV_VAL CSV_VAL_COLNAME Function UDF to read a CSV value from a CSV file. CSV_VAL( data, 'column-name' )
CSV_VARCHAR CSV_VARCHAR_COLID Function UDF to read a CSV value as a vachar(2048) value from a CSV file. CSV_VARCHAR( data, relative-column-number )
CSV_VARCHAR CSV_VARCHAR_COLNAME Function UDF to read a CSV value as a vachar(2048) value from a CSV file. CSV_VARCHAR( data, 'column-name' )
CURLIB IQ_CURLIB Function UDF that returns the job's current library. e.g., values iQuery.curlib() into <host-variable>;
CVTDATE IQ_CVTDATE_C2DT 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;
CVTDATE IQ_CVTDATE_D2DT 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;
CVTOBJDTS IQ_CVTOBJDATE2DTS Function UDF that converts a 13-character outfile-style '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.
DATEDUR IQ_DATEDUR Table Function
DECDATE IQ_DATE2DEC Function
DECDATE IQ_DATE2DEC_CURDATE Function
DECDATE IQ_DATE2DEC_DATEFMTONLY Function
DECDATE IQ_DATE2DEC_DATEONLY Function
DECEDIT IQ_DECEDIT Function
DECEDIT IQ_DECEDIT_DFTLEN Function
DTAARA IQ_DTAARA Table Function UDTF to retrieve all or part of a data area. DTAARA('<library-name'>,<'data-area-name'> [, start-pos ] [, length ]) The DTAARA() UDTF is new in iQuery V5R1. It is similar to the IBM-provided DATA_AREA_INFO() UDTF. The library name is optional and if omitted, then the 1st parameter is the data area name. e.g., SELECT * FROM table( dtaara('QGPL','MYDATAAREA',5,8)) da;
DTAARA IQ_DTAARAEX Table Function UDTF to retrieve all or part of a data area. DTAARA('<library-name/>data-area-name' [, start-pos ] [, length ]) The DTAARA() UDTF is new in iQuery V5R1. It is similar to the IBM-provided DATA_AREA_INFO() UDTF. The data area name is one parameter and may be qualifed using traditional qualified name syntax e.g., 'qgpl/mystuff' e.g., SELECT * FROM table( dtaara('QGPL/MYDATAAREA',5,8)) da;
ENCODE_TAG IQ_ENCODE_TAG 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.encode_TAG(itemDesc) from mydata.salesHist where region = 'CHICAGO';
ENCODE_URL IQ_ENCODE_URL 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.encode_URL(itemnbr,[ccsid:1208]) from mydata.saleshist where region = 'CHICAGO';
ENCODE_XML IQ_ENCODE_XML Function
FLDLIST IQ_FIELDLIST Table Function UDTF to retrieve the list of fields (column names) for a file (Table). fldList('library','database-file'); This provides a list of columns and descriptions similar to what we provide with the iQuery WRKIQRY CL command.
FROMHEX 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'
FTPDIR 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');
GETCPUCOUNT IQ_GETCPUCOUNT Function UDF to return the number of active cores on this system (all partitions. e.g., VALUES getCPUcount() INTO <host-variable>;
GETCURLIB IQ_GETCURLIB Function UDF that returns the job's current library. e.g., values iQuery.getcurlib() into <host-variable>;
GETENV IQ_GET_ENVVAR_VALUE Function UDF to retrieve an Environment Variable's value. The GETENV() UDF returns the specified value for the Environment variable. e.g., VALUES getenv('JAVA_HOME') INTO <host-variable>;
GETOBJSYSNAME 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>;'
GETSHORTNAME IQ_GETSHORTNAME Function UDF that returns the short, 10-character System Object 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>;
GETSRLNBR IQ_GETSRLNBR Function UDF to return the system serial number. e.g., VALUES GETSRLNBR() INTO <host-variable>;
GETSYSNAME IQ_GETSYSNAME Function UDF to return the system name. e.g., VALUES GETSYSNAME() INTO <host-variable>;
IFSCOPY IQ_CPY_IFS_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');
IFSCOPY IQ_CPY_IFS_UDF 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>;
IFSDELETE IQ_DEL_IFS_PROC Procedure Procedure that deletes an IFS file. e.g., call iQuery.ifsDelete('/home/cozzi/fromfile.txt');
IFSDELETE IQ_DEL_IFS_UDF Function UDF that deletes an IFS file. e.g., values iQuery.ifsDelete('/home/cozzi/fromfile.txt');
IFSDIR IQ_DIR_IFS 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;
IFSEXISTS IQ_EXISTS_IFS Function UDF that checks if the specified IFS file exists. The return value is 1 if the file exists. If the file does not exist, it returns NULL. e.g., values iQuery.ifsExists('/home/cozzi/fromfile.txt'); into <host_variable>;
IFSFILE IQ_FILE_IFS Table Function UDTF to view the contents of an IFS text file. e.g., select * from table( iQuery.ifsFile('/home/cozzi/fromfile.txt') ) ifs;
IFSRCOPY IQ_RMTCOPY_IFS 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');
IFSSTAT IQ_STAT64 Table Function UDTF that returns the attributes from the stat64 structure of the specified IFS file. Things likes size, authority and last accessed/changed/modified dates are returned. e.g., iQuery.ifsStat('/home/cozzi/fromfile.txt');
IQVER 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>;
IQVRM 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>;
JOB IQ_GETJOB_COMPONENT Function UDF that returns the portion of the job name requested. The user may request the JOB, 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. To retrieve the entire full-qualified name, specify job('JOB') or simply job() to return the 3-part job name. e.g., values iQuery.job('jobnbr') into <host-variable>;
JOB_ATTR IQ_JOB_ATTR Table Function UDTF that returns a one-row table containing the job attributes. This UDTF returns most of the information from the QUSRJOBI API formats JOBI0300 and JOBI0400. provides access to the JOB_ATTR() UDTF for the job querying that view. e.g., select * from table( iQuery.job_attr() ) joba;
JOB_DATE IQ_JOB_DATE Function UDF that returns the job's runtime job date. This can be different from the SQL CURRENT_DATE and the System Name. It is the value change via the CHGJOB command's DATE(xxxxx) parameter. This UDF is the same as the iQuery.JOBDATE() UDF. e.g., values iQuery.job_date() into <host-variable>;
JOBDATE IQ_JOBDATE Function UDF that returns the job's runtime job date. This can be different from the SQL CURRENT_DATE and the System Name. It is the value change via the CHGJOB command's DATE(xxxxx) parameter. This UDF is the same as the iQuery.JOB_DATE() UDF. e.g., values iQuery.jobdate() into <host-variable>;
JOBLOG IQ_JOBLOG Procedure Procedure to write text to the joblog. The first parameter is the text to be written. It is accepts up to 4096 bytes. Parameter 2 is optional and may be the message type, such as *INFO, *DIAG, *COMP, etc. The default is *INFO. The options are the same as the SNDPGMMSG CL command. exec sql call iQuery.joblog('Customer ' + %char(custno) + ' not found.');
KEYLIST IQ_KEYLIST Table Function UDTF to return information about the key fields of the specified file/table. e.g., select seqnbr,keyfld,datatype from table(iQuery.keyList('MYDATA','CUSTMAST')) kl;
LIB_LIST 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;
LICPGM IQ_LICPGM_INT_LICOPT Function
LICPGM IQ_LICPGM_PGMID_ALPHA_RELLVL Function
MBRLIST 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.
MCHINFO 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;
MD5_BINARY IQ_MD5_HASH_BIN Function UDF to return the MD5 hash in "binary" format (16-byte form). Unlike MD5_HASH(), MD5_BINARY() returns a result that normally includes non-printable characters. The result is a 16-character value. This implementation converts the input data to ASCII UTF-8 (CCSID 1208) 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.userFile set USRPWD = md5_binary(:usrpwd) where userID = :usrprf;
MD5_HASH 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 UTF-8 (CCSID 1208) 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.userFile where md5_hash(:usrPwd) = savedPwd;
MONTHENDDATE IQ_GETMONTH_END Function UDF to return the date of the last day of the month for the input date. This UDF accepts one input parameter: a date. The returned value is the date of the last day of the month for that input date. e.g., values iQuery.monthEndDate( current_Date ) into :endOfMonth;
MONTHSTARTDATE IQ_GETMONTH_START Function UDF to return the date of the 1st day of the month for the input date. This UDF accepts one input parameter: a date. The returned value is the date of the 1st day of the month for that input date. e.g., values iQuery.monthStartDate( current_Date ) into :firstOfMonth;
OBJ_DMG 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;
OBJ_EXISTS IQ_OBJ_EXISTS Function This UDF is checks if the specified IBM i object exists. If the object exists, the UDF return 1, otherwise it returns 0. obj_existS('LIBRARY', 'OBJECT-NAME', [, 'object-type' default is *FILE ]) or obj_existS('OBJECT', [, 'object-type' default is *FILE ]) Long SQL names and short System Names may be specified. e.g., VALUES iQuery.obj_exists('QIWS','QCUSTCDT','FILE') INTO :exists; or this 2nd example looks for a data area on the library list: e.g., VALUES iQuery.obj_exists('DEVTEST','*DTAARA') INTO :dataAreaFound;
OBJ_EXPORTS 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;
OBJ_LIST 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;
OBJ_STRUCT 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;
OBJEXISTS IQ_OBJEXISTS Function This UDF is checks if the specified IBM i object exists. If the object exists, the UDF return 1, otherwise it returns 0. OBJEXISTS('LIBRARY', 'OBJECT-NAME', [, 'object-type' default is *FILE ]) or OBJEXISTS('OBJECT', [, 'object-type' default is *FILE ]) Long SQL names or short System Names may be specified. e.g., VALUES iQuery.objexists('QIWS','QCUSTCDT','FILE') INTO :exists; or this 2nd example looks for a data area on the library list: e.g., VALUES iQuery.objexists('DEVTEST','*DTAARA') INTO :dataAreaFound;
OSVER 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>;
OSVRM 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>;
OVRDBF_MBR OVRDBF_FILE_FILE_MBR Procedure
OVRDBF_MBR OVRDBF_FILE_MBR Procedure
OVRDBF_MBR OVRDBF_MBR Procedure
OVRDBFEX_MBR OVRDBFEX_MBR Procedure
PRINTPDF 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.
PRINTPDF 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).
RCOPY IQ_RECURSIVECOPY_IFS Procedure
RIGHTADJUST RIGHTADJUST Function
RMVLIBL IQ_RMVLIBL Procedure
RMVLIBLE IQ_RMVLIBLE Procedure Procedure that that removes the separated list of library names from the user-portion of the library list. e.g., call iQuery.RMVLiblE('IQUERY COZTOOLS RPGOPEN');
RTVCMDD 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;
RTVJOBA IQ_RTVJOBA Table Function UDTF that returns a one-row resultset with the specified Job's job attributes. e.g., select * from table( iQuery.rtvjoba('qualifed/job/name')) jobA;
RTVJOBD 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;
RTVLASTSPLF IQ_RTVLASTSPLFID Table Function UDTF that returns a resultSet (table) with information that identifies the SPOOL file created by the job running this UDTF. There are no parameters. e.g., select * from table( iQuery.rtvlastSplf() ) splfInfo;
RTVLIBD IQ_RTVLIBD Table Function UDTF that returns a one-row resultset with the specified Library's descriptive information. The library text, size (in bytes) ASP, object count, etc. are returned. e.g., select * from table( iQuery.rtvlibD('IQUERY')) ld;
RTVMBRD 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;
RTVNETA 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>;
RTVOBJD 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;
RTVRCDFMT IQ_RCDFMT Table Function UDTF to return the record formats for a file. This UDTF runs the QUSLRCD API and returns the data from format 0200. The Format name, text, field count and record length are returned.
RTVSPLFA IQ_RTVSPLFA Table Function UDTF that returns a resultSet (table) with information about the specified SPOOLED file. There are no parameters. e.g., select * from table( iQuery.rtvSplfA('job','spool file name'[,splnbr]) ) splfa;
SETJAVAPROP IQ_SETJAVA_PROP Function
SETJAVAPROPERTY IQ_SETJAVAPROPERTY Function
SETJAVATLS IQ_SETJAVATLS Function
SETJAVATLS IQ_SETJAVATLS_PROC Procedure
SETJVAPROP IQ_SETJVA_PROP Function
SRLNBR IQ_SRLNBR Function UDF to return the system serial number. e.g., VALUES SRLNBR() INTO <host-variable>;
SYSNAME IQ_SYSNAME Function UDF to return the system name. e.g., VALUES SYSNAME() INTO <host-variable>;
TS_FMT TS_FMT Function
USRSPC 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>;
USRSPC 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>;
WATCHLIST IQ_WATCHLIST Table Function UDTF to return a list of active Watches along with other related information. select * from table(iQuery.watchlist())) wl;

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

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