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
Function Function
Type
DESCRIPTION
74 records retrieved.
BINARY_MD5FunctionUDF 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;
CACHEBATTERYTable FunctionUDTF 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;
CHKLIBLFunctionUDF 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>;
CHKLIBLEFunctionUDF 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>;
CHKOBJFunctionUDF to check if an object exits. Specify the library name, object name and optional object type. (same as the CHKOBJEXISTS() UDF) 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;
CHKOBJEXISTSFunctionUDF to check if an object exits. Specify the library name, object name and optional object type. (same as the CHKOBJ() UDF) Syntax: CHKOBJEXISTS('LIBRARY', 'OBJECT-NAME', [, 'object-type' default is *FILE ]) The UDF returns '1' if the object exists otherwise it returns '0'. e.g., VALUES iQuery.chkobjexists('QIWS','QCUSTCDT') INTO :exists;
CPUCOUNTFunctionUDF to return the number of active cores on this system (all partitions. e.g., VALUES cpuCount() INTO <host-variable>;
CSVTable FunctionUDTF 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_BIGDECFunctionUDF to read a CSV value as a big packed decimal(63,15) value from a CSV file. CSV_BIGDEC( data, 'column-name' )
CSV_BIGDECFunctionUDF to read a CSV value as a big packed decimal(63,15) value from a CSV file. CSV_BIGDEC( data, relative-column-number )
CSV_COUNTFunctionUDF 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_DATEFunctionUDF to read a CSV value as a date from a CSV file. CSV_DATE( data, relative-column-number, 'input-csv-date-format' )
CSV_DATEFunctionUDF to read a CSV value as a date from a CSV file. CSV_DATE( data, 'column-name', 'input-csv-date-format' )
CSV_DECFunctionUDF to read a CSV value as a packed decimal value from a CSV file. CSV_DEC( data, 'column-name' )
CSV_DECFunctionUDF to read a CSV value as a packed decimal value from a CSV file. CSV_DEC( data, relative-column-number )
CSV_INTFunctionUDF to read a CSV value as an integer from a CSV file. CSV_INT( data, 'column-name' )
CSV_INTFunctionUDF to read a CSV value as an integer from a CSV file. CSV_INT( data, relative-column-number )
CSV_VALFunctionUDF to read a CSV value from a CSV file. CSV_VAL( data, relative-column-number )
CSV_VALFunctionUDF to read a CSV value from a CSV file. CSV_VAL( data, 'column-name' )
CURLIBFunctionUDF that returns the job's current library. e.g., values iQuery.curlib() into <host-variable>;
CVTDATEFunctionUDF to return a true SQL/DB2 "date" value from a date that is in numeric format. This function supports both numeric a 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). This converts the value 180731 into a true date value (it will return '2018-07-31'). Normally this is used for legacy database fields that contain date information but are not actual DATE data-type fields/columns. This function is also useful on the ORDER BY clause of a SELECT statement: select custnbr, compyname, iQuery.cvtdate(180731,'ymd') from mydata.salesHist;
CVTDATEFunctionUDF to return a true SQL/DB2 "date" value from a date that is in textual (character) format. This function supports both numeric a 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). This converts the value '180731' into a true date value (it will return '2018-07-31'). Normally this is used for legacy database fields that contain date information but are not actual DATE data-type fields/columns. This function is also useful on the ORDER BY clause of a SELECT statement: select custnbr, compyname, iQuery.cvtdate('180731','ymd') from mydata.salesHist;
DATEDIFFFunction
DATEDURTable Function
DECDATEFunction
DECEDITFunction
DTAARAFunctionUDF 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>;
DTAARAFunctionUDF 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>;
DTSDIFFFunction
FROMHEXFunctionUDF to convert a set of hexadecimal character pairs into single-characters. e.g., VALUES fromHex(X'F1F2F3') into <host-variable> returns '123'
GETENVFunctionUDF to retrieve an Environment Variable's value. GETENV('<environment-variable>'). e.g., VALUES getenv('JAVA_HOME') INTO <host-variable>;
GETSHORTNAMEFunctionUDF 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. e.g., values iQuery.getShortName('MyLongSchemaName','This_file_name_is_long') into <host-variable>;'; or: values iQuery.getShortName('MyLongSchemaName','*LIB') into <host-variable>;
HASH_MD5FunctionUDF 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;
IFSCOPYFunctionUDF 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>;
IFSDELETEFunctionUDF that deletes an IFS file. e.g., values iQuery.ifsDelete('/home/cozzi/fromfile.txt');
IFSDIRTable FunctionUDTF 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;
IFSEXISTSFunctionUDF that check if the IFS file exists. e.g., values iQuery.ifsExists('/home/cozzi/fromfile.txt'); into <host_variable>;
IFSFILETable FunctionUDTF to view the contents of an IFS text file. e.g., select * from table( iQuery.ifsFile('/home/cozzi/fromfile.txt') ) ifs;
IQVERFunctionUDF 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>;
IQVRMFunctionUDF 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>;
JOBFunctionUDF 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>;
JOB_ATTRTable FunctionUDTF 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;
JOB_DATEFunctionUDF 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>;
JOBDATEFunctionUDF 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>;
KEYFLDTable Function
LIB_LISTTable FunctionUDTF 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;
LICPGMFunction
MBRLISTTable FunctionUDTF 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.
MCHINFOTable FunctionUDTF 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;
MONTHENDDATEFunction
MONTHSTARTDATEFunction
OBJ_DMGTable FunctionUDTF 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_EXPORTSTable FunctionUDTF 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_LISTTable FunctionUDTF 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_STRUCTTable FunctionUDTF 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;
OBJDATEDTSFunctionUDF 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.
OSVERFunctionUDF 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>;
OSVRMFunctionUDF 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>;
PRIMARYKEYTable Function
RIGHTADJUSTFunction
RTVCMDDTable FunctionUDTF 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;
RTVJOBDTable FunctionUDTF 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;
RTVLASTSPLFTable FunctionUDTF 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;
RTVMBRDTable FunctionUDTF 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;
RTVNETAFunctionUDF 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>;
RTVOBJDTable FunctionUDTF 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;
SRLNBRFunctionUDF to return the system serial number. e.g., VALUES SRLNBR() INTO <host-variable>;
SYSNAMEFunctionUDF to return the system name. e.g., VALUES SYSNAME() INTO <host-variable>;
TAGENCODEFunctionUDF 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';
TIMEDIFFFunction
TS_FMTFunction
URLENCODEFunctionUDF 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';
USRSPCFunctionUDF 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>;
USRSPCFunctionUDF 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>;

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

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