IF (LEFT(®ION,2) = 'IL');

RuniQry 'userlib/qsqlsrc(myscript)'

select * from table(iQuery.ifsFile(''/file.txt'')) text'

select msg_text INTO :msg from table(iquery.rtvmsgid('CPF2101')) msg;

print after(1,*MACRO,*DATE *DATE,'My Cool Report');

• CSV() - Identifies the CSV file
• CSV_VAL() - Map a CSV column to varchar
• CSV_CHAR() - Map a CSV column to varchar
• CSV_VARCHAR() - Map a CSV column to varchar
• CSV_DEC() - Map a CSV column to packed decimal (DECFLOAT34 value)
• CSV_BIGDEC() - Map a CSV column to packed decimal(63,15) value
• CSV_INT() - Map a CSV column to a 4-byte integer
• CSV_BIGINT() - Map a CSV column to an 8-byte integer
• CSV_DATE() - Map a CSV column to date value
• CSV_COUNT() - Counts the number of columns in the CSV file

Country,Population
 China,1439323776
India,1380004385
USA,331002651

select csv_val(data,'country') as "Country", csv_bigint(data,'population') as "Population" 
 from table(iQuery.csv('/home/user/country_pop.csv')) pop;

 Country            Population
 China              1439323776
 India              1380004385
 USA                 331002651

 select * from table(iQuery.obj_list('QSYS','*ALL','*USRPRF','*ALLSIMPLE')) up; 

exec sql CONNECT RESET;

dspvar var(&month),dec(2),range(1 12),"Select month for report";

 eval &myVar = '=';

SetEnv iq_SRCLIB PRODWEB

SetEnv iq_SRCFILE QSQLSRC

eval &ObjType = trimLeft(&objType, '*');

dspvar &file,3,4,char(8),type(*cname),"Network name";

 eval &myData = sst(&HTTPResponse, 1, 200);

savelobasfile '/home/cozzi/label.pcl',&myLOB;

eval &text = B64Decode(&myLOB);

savefile '/home/mystuff/data.xls', &salesfigures;

savefile &salesForcast;

print(1,"Date:',*DATE, 'Time:',*TIME);

Date: 09/10/19 Time: 12:27:18

• 'library','object'
• 'object'
• 'library/object'

Select * from table( iQuery.fldList('QCUSTCDT')) ffd

select * from table( iQuery.fldList('qiws/QCustCDT')) ffd

select * from table(iQuery.fldList('qiws','Qcustcdt')) ffd

values iQuery.osvrm() INTO &rmtVRM;

#if (&rmtVRM >= 'V7R3M0');

select objname,          
 #if (&rmtVRM >= 'V7R3M0')    
 objLib,           
 #endif                        
 objtype, objowner, objdefiner as CREATOR, objText   
 FROM table(qsys2.object_Statistics('COZTOOLS','*ALL')) OL;

• accdate - Last Date/Timestamp File was Accessed
• allocsize - File Allocation Size in Bytes
• ccsid - File's data CCSID
• chgdate - Last Date/Timestamp when the File object was changed
• filesize - File's actual size, in bytes.
• links - Number of links to the file ("shortcuts")
• moddate - Last Date/Timestamp when the File's data was changed
• objtype - IBM i Object Type or *STMF or *DIR for IFS objects
• mode - File Mode: Authority Bits/Flags. Contains the following:
  • S_IRGRP S_IROTH S_IRUSR S_IRWXG S_IRWXO S_IRWXU
  • S_IWGRP S_IWOTH S_IWUSR
  • S_IZGRP S_IXOTH S_IXUSR
  • S_ISBLK S_ISCHR S_ISDIR S_ISREG S_ISLNK
  • S_ISSOCK S_ISNATIVE S_ISLINK

 #stat '/home/cozzi/salesinfo.txt';     
 values timestamp('&accdts') INTO &LastAccess; 

 #stat  '/home/cozzi/pickles.jpg', 'PIC_';     
 #stat  '/home/cozzi/apples.gif', 'APPLE_';    
 if (&pic_fileSize > &Apple_FileSize); 
 #msg Pickles is larger than Apples           
 endif; 

 savefile [save-as file name] , &htmlFormField ,  [ccsid]; 

exec sql CALL iQuery.joblog('Hello World!');

select * from table(iquery.job_attr('012345/COZZI/DISP01')) j;

• #stmtView DISABLE
• #stmtSave DISABLE
• #stmtFldList DISABLE

SET OPTION commit=*CHG, datfmt=*ISO;

1. iqLoadSQL - Load SQL from an SQL iQuery Script
2. iqSetVar - Set an SQL iQuery Script Session Variable Value
3. iqGetVar - Get an SQL iQuery Script Session Variable Value
4. iqClearVars - Clear all existing SQL iQuery Script Session Variables
5. iqDumpVars - Write the names and values of each Session Varaible to the joblog
6. iqSetVarInt - Set an SQL iQuery Script Session Variable using an Integer
7. iqSetVarDec - Set an SQL iQuery Script Session Variable using Decimal

runiqry 'select varchar("DEFAULT", 500) from qsys2.sysvars'

IF EXISTS *LIBL/CUSTMAST *FILE;

If (&SQLSTATE < '02000'); 
vs
IF ('&SQLSTATE' < '02000');

1. #OUTPUT
2. OUTPUT() parameter
3. #DFTOUTPUT

#define &var = 20;   
 if (&var = '*ALL');       
 ...                      
 end if; 

eval &myval = sst(&userName,1,len(&username)-1);

• Result messages were not showing up in some situations.
• The *RDB symbol was not being initialized when *LOCAL is used.
• FOREACH INTO clause was not being recognized unless it immediately preceeded the FROM clause.
• Legacy Session Variables &1 throug &999 were not being substituted properly when adjacent to other content. For example: &1CUST would fail but &1 CUST would not
• Session Variable assignments were not dynamically changing type when initial value was text and subsequent value(s) were numeric.
• The SST() built-in function was sometimes failing when more than one SST() was detected on the same conditional (IF) statement.
• AutoQuote was being ignored on the VALUES ... INTO statement
  • The AutoQuotes switch is normally ignored by all standard SQL statements except VALUES... INTO

• ELSEIF stmt was not properly processed for session variables
• #INCLUDE stmt returned text was ignored when it is the final statement
• ELEMS() built-in function was ignored in certain contexts
• #DumpVars was showing first session variable's value when arrays were used.
• isEmpty() built-in function was returning false if the session variable was undefined

• Concat in mixed expressions now works
• Nested #INCLUDES properly release the SQL engine
• New #HTTP_CONTENTTYPE control added to override default
• New BLING command to assign Session Variable Symbol(s) (similar to #PREFIX that was previously available)
• iQuery for web is now thread-safe
• Fixed a bug in numeric expression with parens (in free format)
• Fixed issue with #INCLUDEs that were being called even if enclosed in an IF condition that tested false
• Dataset name in JSON output can be set to *NONE to promote results one level
• JSON column/field names controls have been added
• JSON column/field attributes generation is now controllable
• iQuery for web now supports << directive to write directly to browser
• #WATCH command allows users to trace a Session variable each time it changes. The #UNWATCH or #ENDWATCH commands terminate the watch
• The EVAL/CHGVAR commands are now smarter when mixing text and numbers.
• Corrected a performance issue with FOREACH, VALUES and SELECT INTO clauses
• Built-in functions may now be nested as parms of other built-in functions
• Introduced several iQ v4.1 built-in functions
  • firstOf() - Find first of a list of characters
  • firstNotOf() - Find first char not of list of characters
  • getcookie() - Read HTTP Cookie value
  • msgid() - Get message text using MSG ID
  • getfile() - Read IFS text file into a Session Variable
  • tempname()/tmpname() - Create a temporary IFS file and return its name
  • strlen/len - Return length of contents of Session Variable
  • isWeb() - Returns TRUE if running from the HTTP server
• Introduced several iQ v4.1 commands
  • writestmf - Write content of Session Variable to IFS text file adding the data to the end of the file
  • savestmf - Write content of Session Variable to IFS text file, replacing any existing data in the file
  • setcookie - Create an HTTP cookie that is sent to the browser
  • coltotals - Identify the columns to be auto-totalled when creating excel, print, csv, SyLK and some other output formats
  • lvlbrk/levelbreak - Identify columns to output only when their value changes
  • cout/stdout - Send data to the standard output device
  • CL: is now a hybrid command. It allows multiple lines for the CL command, which must now be terminated with a semicolon. This is for compatibility with IBM RUNSQLSTM.
  • FTP - Users may now generate FTP scripts using iQuery Script as a scripting tool. See documentation for details of options and features

1. V7R1 PTF - SI61475 & SI61887
2. V7R2 PTF - SI61129 & SI61155
3. V7R3 PTF - SI62005 & SI62028

eval &myVar = '12345 * 3741 + 16';

eval &myVar = 12345 * 3741 + 16;

1. V7R1 PTF
• SI61475 & SI61887
2. V7R2 PTF
• SI61129 & SI61155
3. V7R3 PTF
• SI62005 & SI62028

#Define &var = 3742;