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, and of course SQL iQuery.
As we add documentation for each UDTF/UDF/Proc the name will be underlined and linked to its documentation. Click the link to read the documentation.
| Function Name |
Specific Name |
Function Type |
DESCRIPTION | |
|---|---|---|---|---|
| ACTGRP | IQ_ACTGRP | Table Function | Returns a list of Activation Groups and their properties as resultSet rows. This Function is being deprecated. See the enhanced ACTGRP_LIST() UDTF for futher information on generating a list of activation groups. | |
| CHGCURLIB | IQ_FCURLIB | Function | UDF to change the job's current library to what is specified by the user. | |
| CHGCURLIB | IQ_PCURLIB | Procedure | Stored Procedure to change the job's current library to what is specified by the user. | |
| CONVERT_DATE | IQ_CVTDATE_DEC | Function | ||
| CONVERT_DATE | IQ_CVTDATE_CHAR | Function | ||
| CSV_BIGDEC | IQ_CSV_BIGDEC_COLID | Function | ||
| CSV_BIGDEC | IQ_CSV_BIGDEC_COLNAME | Function | ||
| CSV_BIGINT | IQ_CSV_VAL_BIGINT_COLID | Function | ||
| CSV_BIGINT | IQ_CSV_VAL_BIGINT_COLNAME | Function | ||
| CSV_COUNT | IQ_CSV_COLUMN_COUNT | Function | ||
| CSV_DATE | IQ_CSV_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_DATE | IQ_CSV_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_DEC | IQ_CSV_VAL_DEC34_COLID | Function | ||
| CSV_DEC | IQ_CSV_VAL_DEC34_COLNAME | Function | ||
| CSV_INT | IQ_CSV_VAL_INT_COLNAME | Function | ||
| CSV_INT | IQ_CSV_VAL_INT_COLID | Function | ||
| CSV_VAL | IQ_CSV_VAL_COLID | Function | ||
| CSV_VAL | IQ_CSV_VAL_COLNAME | Function | ||
| CSV | IQ_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. | |
| DTAARA | IQ_DTAARAEX | Table Function | ||
| DTAARA | IQ_DTAARA | Table Function | ||
| IFSACCESS | IQ_IFSACCESS | Function | UDF to check if the IFS file exists for the specified mode (read/write/execute). | |
| IFSACCESS | IQ_IFSACCESS_M | Function | UDF to check if the IFS file exists for the specified mode (read/write/execute). | |
| IFSDELETE | IQ_IFSDEL | Function | ||
| IFSDELETE | IQ_IFSDEL_PROC | Procedure | ||
| IFSPATH | IQ_IFSPATH | Table Function | UDTF to read an IFS directory and return the entries in that directory. | |
| IFSSTAT | IQ_IFSSTAT | Table Function | UDTF to return the attributes of the specified IFS file. The data returned is similar to the IFS stat64() API. | |
| MBRLIST | IQ_MBRLIST | Table Function | Retrieve a list of Member names and their attributes | |
| OBJEXISTS | IQ_OBJEXISTS | Function | ||
| PRINTPDF | IQ_PRTPDF | Function | ||
| PRINTPDF | IQ_PRTPDFP | Procedure | ||
| RTVJOBA | IQ_RTVJOBA | Table Function | ||
| RTVLASTSPLF | IQ_RTVLASTSPLF | Table Function | Retrieve Last SPOOLED File Info (RTVLASTSPLF) UDTF. This UDTF returns the name, number and other properties of the most recently created SPOOLED file for the job running the UDTF. | |
| RTVMSGD | IQ_RTVMSGD | Table Function | Retrieve Message ID Description (RTVMSGD) UDTF | |
| RTVNETA | IQ_RTVNETA | Function | ||
| RTVOBJD | IQ_RTVOBJD | Table Function | Retrieve Object Description (RTVOBJD) UDTF | |
| RTVOBJLCK | IQ_OBJLOCKS | Table Function | ||
| RTVRCDFMT | IQ_RCDFMT | Table Function | ||
| RTVUSRPRF | IQ_RTVUSRPRF | Table Function | Retrieve User Profile (RTVUSRPRF) UDTF | |
| ADDLIBL | IQ_ADDLIBL | Procedure | Add a Library to the Library list (same as ADDLIBLE) | |
| ADDLIBLE | IQ_ADDLIBLE | Procedure | Add a Library to the Library list (same as ADDLIBL) | |
| CACHEBATTERY | IQ_CACHEBATTERY | Table Function | ||
| CHARTODEC | IQ_DEEDIT | Function | UDF to convert textual numeric values with edit symbols to decimal. For example, a text value of '$12,465.72CR' cannot be converted with standard SQL interfaces. the 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 notation symbol (in that order). | |
| CHGLIBL | IQ_CHGLIBL | Procedure | ||
| CHKEXISTS | IQ_IFSCHKEXIST | Function | UDF to check if the IFS file exists. It returns 1 if it exists, otherwise it returns 0. An alternate version is named IFSEXISTS(). is similar except when not found it returns NULL instead of 0. | |
| CHKLIBL | IQ_CHKLIBL | Function | ||
| CHKLIBLE | IQ_CHKLIBLE | Function | ||
| CHKOBJ | IQ_CHECKOBJECT | Function | ||
| CMDLOG | IQ_CMDLOG | Procedure | ||
| COPYTOPDF | IQ_CP2PDF2 | Function | ||
| CPUCOUNT | IQ_CPUCOUNT | Function | UDF to return the number of CPU "cores" active for the entire system (all partitions). The returned value is an integer. | |
| CPYOUTQ_STMF | IQ_PCPOUTQ | Procedure | ||
| CPYTOPDF | IQ_CP2PDF | Function | ||
| CPYTOPDF | IQ_CP2PDFP | Procedure | ||
| CRTUSRSPC | IQ_CRTUSRSPACE | Procedure | ||
| CSV_CHAR | IQ_CSV_CHAR_COLID | Function | ||
| CSV_CHAR | IQ_CSV_CHAR_COLNAME | Function | ||
| CSV_DATEEX | IQ_CSV_DATEEX_COLNAME | Function | ||
| CSV_DATEEX | IQ_CSV_DATEEX_COLID | Function | ||
| CSV_VARCHAR | IQ_CSV_VARCHAR_COLID | Function | ||
| CSV_VARCHAR | IQ_CSV_VARCHAR_COLNAME | Function | ||
| CURLIB | IQ_CURLIB | Function | ||
| CVTDATE | IQ_CVTDATC | Function | ||
| CVTDATE | IQ_CVTDATN | Function | ||
| CVTOBJDTS | IQ_CVTOBJDTS | Function | ||
| CVTTODTS | IQ_CVTYMDTEXT2DTS | Function | ||
| DATEDUR | IQ_DATEDUR | Table Function | ||
| DECDATE | IQ_DATE2DEC_CURDATE | Function | ||
| DECDATE | IQ_DATE2DEC | Function | ||
| DECDATE | IQ_DATE2DEC_DATEFMTONLY | Function | ||
| DECDATE | IQ_DATE2DEC_DATEONLY | Function | ||
| DECEDIT | IQ_DECEDIT_DFTLEN | Function | ||
| DECEDIT | IQ_DECEDIT | Function | ||
| ENCODE_TAG | IQ_ENCODE_TAG | Function | ||
| ENCODE_URL | IQ_ENCODE_URL | Function | ||
| ENCODE_XML | IQ_ENCODE_XML | Function | ||
| FROMHEX | IQ_FROMHEX | Function | UDF to return convert 2-bytes into 1-character, or convert from hex to character. | |
| GETCPUCOUNT | IQ_GETCPUCOUNT | Function | UDF to return the number of CPU "cores" active for the entire system (all partitions). The returned value is an integer. | |
| GETCURLIB | IQ_GETCURLIB | Function | ||
| GETENV | IQ_GETENV | Function | UDF to return the value of an Environment variable. The value is returned as a VARCHAR(32739) that should be CAST to the length and type desired. | |
| GETOBJSYSNAME | IQ_GETOBJSYSNAME | Function | ||
| GETSHORTNAME | IQ_GETSHORTNAME | Function | ||
| GETSRLNBR | IQ_GETSRLNBR | Function | UDF to return the System Serial Number. This is simlar to using RTVSYSVAL SYSVAL(QSRLNBR). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces. | |
| GETSYSNAME | IQ_GETSYSNAME | Function | UDF to return the partition's system name. This is similar to using RTVNETA SYSNAME(&SYSNAME). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces. | |
| IFSCOPY | IQ_IFSCOPY | Function | ||
| IFSCOPY | IQ_IFSCOPY_PROC | Procedure | ||
| IFSDIR | IQ_IFSDIR | Table Function | UDTF to read an IFS directory and return the entries in that directory. NOTE: This UDTF is replaced by the IFSPATH() UDTF however IFSIDR (this UDTF) is now a wrapper for IFSPATH() so it should continue to function into the future. | |
| IFSEXISTS | IQ_IFSEXISTS | Function | UDF to check if the IFS file exists. It returns 1 if it exists, otherwise it returns NULL. An alternate version is named CHKEXISTS(). is similar except when not found it returns 0 instead of NULL. | |
| IFSRCOPY | IQ_IFSRCOPY | Procedure | ||
| IFSREAD | IQ_IFSREAD | Table Function | UDTF to read IFS text content and return it one row at a time to the user. When used with the RUNiQRY command, the entire file can be viewed and processed by manipulating the TEXTDATA return column. Note that this UDTF when used without options is functionally similar to the IBM i DSPF command. | |
| IQVER | IQ_VER | Function | ||
| IQVRM | IQ_VRM | Function | ||
| JOB | IQ_GETJOB_COMPONENT | Function | UDF to retrieve one part of the job identity or the qualified job name. | |
| JOB_ATTR | IQ_JOB_ATTR | Table Function | ||
| JOB_DATE | IQ_JOB_DATE | Function | ||
| JOBDATE | IQ_JOBDATE | Function | ||
| JOBLOG | IQ_JOBLOG | Procedure | ||
| LIB_LIST | IQ_LIBLIST | Table Function | ||
| LICPGM | IQ_LICPGM_INT_LICOPT | Function | ||
| LICPGM | IQ_LICPGM_PGMID_ALPHA_RELLVL | Function | ||
| MCHINFO | IQ_MCHINFO | Table Function | ||
| MONTHENDDATE | IQ_GETMONTH_END | Function | ||
| MONTHSTARTDATE | IQ_GETMONTH_START | Function | ||
| OBJ_DMG | IQ_DMG_OBJLIST | Table Function | ||
| OBJ_EXISTS | IQ_OBJ_EXISTS | Function | ||
| OBJ_EXPORTS | IQ_OBJEXPORTS | Table Function | ||
| OBJ_LIST | IQ_OBJLIST | Table Function | ||
| OBJ_STRUCT | IQ_OBJSTRUCT | Table Function | ||
| OBJTYPES | IQ_OBJTYPES | Table Function | ||
| OSVER | IQ_OSVER | Function | UDF to return the version of IBM i running. The version is returned as a Dec(7,2) with the primary version to the left of the decimal notation, and the Release level in the decimals to the right of the decimal notation. | |
| OSVRM | IQ_OSVRM | Function | UDF to return the version of IBM i running. The version is returned as a VARCHAR(10) with the format VxRyMz. | |
| OVRDBF_MBR | IQ_OVRDBF_FILE_FILE_MBR | Procedure | ||
| OVRDBF_MBR | IQ_OVRDBF_FILE_MBR | Procedure | ||
| OVRDBF_MBR | IQ_OVRDBF_MBR | Procedure | ||
| OVRDBFEX_MBR | IQ_OVRDBFEX_MBR | Procedure | ||
| QRUNCMD | IQ_QCAPCMF | Function | Run a CL command using QCAPCMD. This is the FUNCTION version that may be used within an SQL Select statement. The returned value is the CPF MSGID (if any) generated by the CL command that is run. | |
| QRUNCMD | IQ_QCAPCMD | Procedure | Run a CL command using the QCAPCMD API. This Procedure is also used internally by the SQL iQuery Script processor to run CL commands via the "CL:" directive. QCAPCMD provides more features than the QCMDEXC API in that it can be instructed to run, syntax-check, restricted users with *LIMIT capabilities, and prompt the command autmatically. Some of those additional features are enabled in this function. | |
| RCOPY | IQ_IFS_RCOPY | Procedure | ||
| RIGHTADJUST | RIGHTADJUST | Function | ||
| RMVLIBL | IQ_RMVLIBL | Procedure | ||
| RMVLIBLE | IQ_RMVLIBLE | Procedure | ||
| RTVMBRD | IQ_RTVMBRD | Table Function | ||
| RTVMSGID | IQ_RTVMSGID | Table Function | Retrieve Message ID Descr (RTVMSGID) UDTF | |
| RTVSPLFA | IQ_RTVSPLFA | Table Function | Retrieve SPOOLED File Attributes (RTVSPLFA) UDTF | |
| SPLF_DATA | IQ_SPLF_DATA | Table Function | Read SPOOL File Data for V7R2M0 (378-byte wide result) | |
| SPOOLED_FILE_DATA | IQ_SPLF_DATA_DATA378 | Table Function | ||
| SRLNBR | IQ_SRLNBR | Function | UDF to return the System Serial Number. This is simlar to using RTVSYSVAL SYSVAL(QSRLNBR). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces. | |
| SYSNAME | IQ_SYSNAME | Function | UDF to return the partition's system name. This is similar to using RTVNETA SYSNAME(&SYSNAME). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces. | |
| TOASCII | IQ_TOASCII | Function | ||
| TODATE | IQ_TODATE | Function | Convert text input into DATE value similar to SQL TO_DATE, but with CYMD support. | |
| TODTS | IQ_TODTS | Function | Convert text input into TimeStamp value. | |
| TOEBCDIC | IQ_TOEBCDIC | Function | ||
| USRSPC | IQ_RTVUSRSPACEEX | Function | ||
| USRSPC | IQ_RTVUSRSPACE | Function | ||
| 134 records retrieved. | ||||
Check out SQL iQuery for yourself. Download our free trial today.