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, 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.

SQL iQuery UDF/UDTF Function List
UDF, UDTF, and Procedures Listing
Function
Name
Specific
Name
Function
Type
DESCRIPTION
ACTGRPIQ_ACTGRPTable FunctionReturns 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.
CHGCURLIBIQ_FCURLIBFunctionUDF to change the job's current library to what is specified by the user.
CHGCURLIBIQ_PCURLIBProcedureStored Procedure to change the job's current library to what is specified by the user.
CONVERT_DATEIQ_CVTDATE_DECFunction
CONVERT_DATEIQ_CVTDATE_CHARFunction
CSV_BIGDECIQ_CSV_BIGDEC_COLIDFunction
CSV_BIGDECIQ_CSV_BIGDEC_COLNAMEFunction
CSV_BIGINTIQ_CSV_VAL_BIGINT_COLIDFunction
CSV_BIGINTIQ_CSV_VAL_BIGINT_COLNAMEFunction
CSV_COUNTIQ_CSV_COLUMN_COUNTFunction
CSV_DATEIQ_CSV_DATE_COLNAMEFunctionUDF to read a CSV value as a date from a CSV file. CSV_DATE( data, 'column-name', 'input-csv-date-format' )
CSV_DATEIQ_CSV_DATE_COLIDFunctionUDF to read a CSV value as a date from a CSV file. CSV_DATE( data, relative-column-number, 'input-csv-date-format' )
CSV_DECIQ_CSV_VAL_DEC34_COLIDFunction
CSV_DECIQ_CSV_VAL_DEC34_COLNAMEFunction
CSV_INTIQ_CSV_VAL_INT_COLNAMEFunction
CSV_INTIQ_CSV_VAL_INT_COLIDFunction
CSV_VALIQ_CSV_VAL_COLIDFunction
CSV_VALIQ_CSV_VAL_COLNAMEFunction
CSVIQ_CSV_UDTFTable 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.
DTAARAIQ_DTAARAEXTable Function
DTAARAIQ_DTAARATable Function
IFSACCESSIQ_IFSACCESSFunctionUDF to check if the IFS file exists for the specified mode (read/write/execute).
IFSACCESSIQ_IFSACCESS_MFunctionUDF to check if the IFS file exists for the specified mode (read/write/execute).
IFSDELETEIQ_IFSDELFunction
IFSDELETEIQ_IFSDEL_PROCProcedure
IFSPATHIQ_IFSPATHTable FunctionUDTF to read an IFS directory and return the entries in that directory.
IFSSTATIQ_IFSSTATTable FunctionUDTF to return the attributes of the specified IFS file. The data returned is similar to the IFS stat64() API.
MBRLISTIQ_MBRLISTTable FunctionRetrieve a list of Member names and their attributes
OBJEXISTSIQ_OBJEXISTSFunction
PRINTPDFIQ_PRTPDFFunction
PRINTPDFIQ_PRTPDFPProcedure
RTVJOBAIQ_RTVJOBATable Function
RTVLASTSPLFIQ_RTVLASTSPLFTable FunctionRetrieve 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.
RTVMSGDIQ_RTVMSGDTable FunctionRetrieve Message ID Description (RTVMSGD) UDTF
RTVNETAIQ_RTVNETAFunction
RTVOBJDIQ_RTVOBJDTable FunctionRetrieve Object Description (RTVOBJD) UDTF
RTVOBJLCKIQ_OBJLOCKSTable Function
RTVRCDFMTIQ_RCDFMTTable Function
RTVUSRPRFIQ_RTVUSRPRFTable FunctionRetrieve User Profile (RTVUSRPRF) UDTF
ADDLIBLIQ_ADDLIBLProcedureAdd a Library to the Library list (same as ADDLIBLE)
ADDLIBLEIQ_ADDLIBLEProcedureAdd a Library to the Library list (same as ADDLIBL)
CACHEBATTERYIQ_CACHEBATTERYTable Function
CHARTODECIQ_DEEDITFunctionUDF 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).
CHGLIBLIQ_CHGLIBLProcedure
CHKEXISTSIQ_IFSCHKEXISTFunctionUDF 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.
CHKLIBLIQ_CHKLIBLFunction
CHKLIBLEIQ_CHKLIBLEFunction
CHKOBJIQ_CHECKOBJECTFunction
CMDLOGIQ_CMDLOGProcedure
COPYTOPDFIQ_CP2PDF2Function
CPUCOUNTIQ_CPUCOUNTFunctionUDF to return the number of CPU "cores" active for the entire system (all partitions). The returned value is an integer.
CPYOUTQ_STMFIQ_PCPOUTQProcedure
CPYTOPDFIQ_CP2PDFFunction
CPYTOPDFIQ_CP2PDFPProcedure
CRTUSRSPCIQ_CRTUSRSPACEProcedure
CSV_CHARIQ_CSV_CHAR_COLIDFunction
CSV_CHARIQ_CSV_CHAR_COLNAMEFunction
CSV_DATEEXIQ_CSV_DATEEX_COLNAMEFunction
CSV_DATEEXIQ_CSV_DATEEX_COLIDFunction
CSV_VARCHARIQ_CSV_VARCHAR_COLIDFunction
CSV_VARCHARIQ_CSV_VARCHAR_COLNAMEFunction
CURLIBIQ_CURLIBFunction
CVTDATEIQ_CVTDATCFunction
CVTDATEIQ_CVTDATNFunction
CVTOBJDTSIQ_CVTOBJDTSFunction
CVTTODTSIQ_CVTYMDTEXT2DTSFunction
DATEDURIQ_DATEDURTable Function
DECDATEIQ_DATE2DEC_CURDATEFunction
DECDATEIQ_DATE2DECFunction
DECDATEIQ_DATE2DEC_DATEFMTONLYFunction
DECDATEIQ_DATE2DEC_DATEONLYFunction
DECEDITIQ_DECEDIT_DFTLENFunction
DECEDITIQ_DECEDITFunction
ENCODE_TAGIQ_ENCODE_TAGFunction
ENCODE_URLIQ_ENCODE_URLFunction
ENCODE_XMLIQ_ENCODE_XMLFunction
FROMHEXIQ_FROMHEXFunctionUDF to return convert 2-bytes into 1-character, or convert from hex to character.
GETCPUCOUNTIQ_GETCPUCOUNTFunctionUDF to return the number of CPU "cores" active for the entire system (all partitions). The returned value is an integer.
GETCURLIBIQ_GETCURLIBFunction
GETENVIQ_GETENVFunctionUDF 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.
GETOBJSYSNAMEIQ_GETOBJSYSNAMEFunction
GETSHORTNAMEIQ_GETSHORTNAMEFunction
GETSRLNBRIQ_GETSRLNBRFunctionUDF 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.
GETSYSNAMEIQ_GETSYSNAMEFunctionUDF 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.
IFSCOPYIQ_IFSCOPYFunction
IFSCOPYIQ_IFSCOPY_PROCProcedure
IFSDIRIQ_IFSDIRTable FunctionUDTF 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.
IFSEXISTSIQ_IFSEXISTSFunctionUDF 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.
IFSRCOPYIQ_IFSRCOPYProcedure
IFSREADIQ_IFSREADTable FunctionUDTF 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.
IQVERIQ_VERFunction
IQVRMIQ_VRMFunction
JOBIQ_GETJOB_COMPONENTFunctionUDF to retrieve one part of the job identity or the qualified job name.
JOB_ATTRIQ_JOB_ATTRTable Function
JOB_DATEIQ_JOB_DATEFunction
JOBDATEIQ_JOBDATEFunction
JOBLOGIQ_JOBLOGProcedure
LIB_LISTIQ_LIBLISTTable Function
LICPGMIQ_LICPGM_INT_LICOPTFunction
LICPGMIQ_LICPGM_PGMID_ALPHA_RELLVLFunction
MCHINFOIQ_MCHINFOTable Function
MONTHENDDATEIQ_GETMONTH_ENDFunction
MONTHSTARTDATEIQ_GETMONTH_STARTFunction
OBJ_DMGIQ_DMG_OBJLISTTable Function
OBJ_EXISTSIQ_OBJ_EXISTSFunction
OBJ_EXPORTSIQ_OBJEXPORTSTable Function
OBJ_LISTIQ_OBJLISTTable Function
OBJ_STRUCTIQ_OBJSTRUCTTable Function
OBJTYPESIQ_OBJTYPESTable Function
OSVERIQ_OSVERFunctionUDF 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.
OSVRMIQ_OSVRMFunctionUDF to return the version of IBM i running. The version is returned as a VARCHAR(10) with the format VxRyMz.
OVRDBF_MBRIQ_OVRDBF_FILE_FILE_MBRProcedure
OVRDBF_MBRIQ_OVRDBF_FILE_MBRProcedure
OVRDBF_MBRIQ_OVRDBF_MBRProcedure
OVRDBFEX_MBRIQ_OVRDBFEX_MBRProcedure
QRUNCMDIQ_QCAPCMFFunctionRun 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.
QRUNCMDIQ_QCAPCMDProcedureRun 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.
RCOPYIQ_IFS_RCOPYProcedure
RIGHTADJUSTRIGHTADJUSTFunction
RMVLIBLIQ_RMVLIBLProcedure
RMVLIBLEIQ_RMVLIBLEProcedure
RTVMBRDIQ_RTVMBRDTable Function
RTVMSGIDIQ_RTVMSGIDTable FunctionRetrieve Message ID Descr (RTVMSGID) UDTF
RTVSPLFAIQ_RTVSPLFATable FunctionRetrieve SPOOLED File Attributes (RTVSPLFA) UDTF
SPLF_DATAIQ_SPLF_DATATable FunctionRead SPOOL File Data for V7R2M0 (378-byte wide result)
SPOOLED_FILE_DATAIQ_SPLF_DATA_DATA378Table Function
SRLNBRIQ_SRLNBRFunctionUDF 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.
SYSNAMEIQ_SYSNAMEFunctionUDF 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.
TOASCIIIQ_TOASCIIFunction
TODATEIQ_TODATEFunctionConvert text input into DATE value similar to SQL TO_DATE, but with CYMD support.
TODTSIQ_TODTSFunctionConvert text input into TimeStamp value.
TOEBCDICIQ_TOEBCDICFunction
USRSPCIQ_RTVUSRSPACEEXFunction
USRSPCIQ_RTVUSRSPACEFunction
134 records retrieved.

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

Copyright 2021 Cozzi Productions, Inc. All Rights Reserved.