AS400

Below command will copy hte WRKFILE from Qtem to a CSV file in Directory DIR. CPYTOIMPF FROMFILE(QTEMP/WRKFILE) TOSTMF('/DIR/FILENAME.CSV') MBROPT(*REPLACE)   STMFCODPAG(*PCASCII) RCDDLM(*CRLF) ADDCOLNAM(*SYS) 

%Char((%Date - %Days(7)):*MDY/) If Current date is 9/29/17 the result of the above piece of code will give 9/22/17 ( ie - 7 days) Extract Date from a Timestamp fDate = %Date(%subst(%Char(%Timestamp()):1:10):*MDY/);  Difference between 2 dates, time or timestamp: %DIFF(op1:op2:*MSECONDS|*SECONDS|*MINUTES|*HOURS|*DAYS|*MONTHS|*YEARS) %DIFF(op1:op2:*MS|*S|*MN|*H|*D|*M|*Y) time_taken = %DIFF (%timestamp() : start_time : *SECONDS); Get Year/Month/Day from Date. and Hour/Min/Second from Timestamp: %SUBDT(value : unit { : digits { : decpos } }) *..1....+....2....+....3....+....4....+....5....+....6....+....7...+.... date = d'1999-02-17'; time = t'01.23.45'; timestamp = z'1999-02-17-01.23.45.98765'; num = %subdt(date:*YEARS); // num = 1999 num = %subdt(time:*MN); // num = 23 seconds = %subdt(timestamp:*S:5:3); // seconds = 45.987

In Subfile control record format: A                                                   SFLCSRRRN(&CSRRN) A            SRRN            4S 0H      SFLRCDNBR(CURSOR) A            CSRRN          5S 0H                         Before exformatting the subfile move CSRRN to SRRN

Save the files FILE1 and FILE2 from Library SLIB to the save file SAVF in TSAVLIBin the source system. SAVOBJ OBJ(FILE1 FILE2) LIB(SLIB) DEV(*SAVF) SAVF(TSAVLIB/SAVF) Then in the Source system Use FTP to put the Content of Save File (TSAVLIB/SAVF) to a save file (TSAVLIB2/SAVF) in the destination system. FTP SYSTEM1 PUT TSAVLIB/SAVF TSAVLIB2/SAVF (Get in Destination System wont run sometimes hence always used PUT) Once FTP is complete. Restore Objects RSTOBJ OBJ(*ALL) SAVLIB(SLIB) DEV(*SAVF) SAVF(TSAVLIB2/SAVF) MBROPT(*ALL) RSTLIB(DESTLIB) When restoring make sure the library is a separate library (Create a New)because this restore will clear and restore the object so you will lose whatever in the restoring library. It is safe clear the library first so that you dont let the system clear it and you can backup whatever you want to keep. --------------------------------------------------------------------------- Save 2 members (/More than 2) from 2 files ...

Update Multiple Field with Value from Fields in another file Below Query can update fields a.Fld1,a.Fld2,a.Fld3,a.Fld4 & a.Fld5  in TBL1 with the values from  b.Bld1 ,b.Bld5,b.Bld3,b.Bld6 & b.Bld8 in TBL2 Below Query will work only if the below query will not have duplicate record select b.fkey1,b.fkey2 from Tabl2 B, TBL1 a               where a.fkey1= b.fkey1 and a.fkey2= b.fkey2 If there is duplicate change the where clause to get unique values then use the same whare claues in the subquery in both places update Tabl1  a set (a.Fld1,a.Fld2,a.Fld3,a.Fld4, a.Fld5  )  =           (select b.Bld1 ,b.Bld5,b.Bld3,b.Bld6,b.Bld8 from Tabl2 B               where a.fkey1= b.fkey1 and a.fkey2= b.fkey2 ) Where (fkey1,fkey2) in (select fkey1,fkey2 from Tbl2)

Control the Way SQL sorts the records based on Values in the tables field: Below piece of SQL statement will let me have control on how the SQL sort the records based on the values in the Status field; apart from SQL sort based on the alphabetic order   order by case  Status                                                 when 'D' then 1                            when 'A' then 2                            when 'X' then 3                         ...

Scenario: Container  field in CONT file is build as Julian Day (3 Digit)+ 6 Digit sequence number. Julian Day (3 Char) is also field in CONT file. Below query will identify all the records that are having Jul_Day and first 3 digit are not matching using Case when condition. select * from Cont where                                                                                                               int(Jul_Day) <> case                     ...