I just got a new laptop with Windows 10 which can't use the "Data Transfer to iSeries" function in excel. I've tried to re-install the add-on again but still does not work.

I'd like to transfer file which has a list of objects, libraries, and saved date for restoration on AS/400 (using BRMS function) For example :

   OBJ          LIB1        LIB2        DATE
   OBJ0001      FROMLIB01   TOLIB01     20/12/2018
   OBJ0002      FROMLIB02   TOLIB02     20/12/2018
   OBJ0003      FROMLIB03   TOLIB03     20/12/2018

So, I need to find the way to edit record in the physical file instead of transferring the file from the PC. I know that STRSQL can edit records but I'm not familiar with the SQL commands. Can anyone guide me on how to edit many records using SQL?


This isn't exactly about AS/400 but about SQL in General. See here for a general overview of the syntax and here for a quick start guide.

For changing records (on the AS/400), the standard UPDATE clause applies. Probably you're not aware that the F4 prompting function also works in interactive SQL (strsql). Try it out, it's definitely helpful for a beginner.

A general starting statement would be UPDATE LIBRARY/TABLENAME SET field1='value1', … WHERE field1='value0'…

The SET Clause lists comma-separated field-value pairs to write (update) and the WHERE clause restricts these updates to the desired records. The WHERE-Clause can be made arbitrarily complex and list many restrictions with AND/OR boolean logic for precise selection of which records to update. See tutorial for WHERE-Clause examples.

| improve this answer | |
  • Thank you very much for your sharing. I've tried to insert record to PF by using STRSQL and it's work, then i tried to use SQL command in my CL program because i need to insert too many record, after compiled and call PGM, The job log tell about "Member OBJ0001 not journaled to journal *N". I use DSPFD to see file attribute and i found that " File is currently journaled . . . . . . . . : No". Do i have to change my PF attribute which attached with journal? How to change this value? – KWAN Feb 6 '19 at 10:42
  • I'm not fluent with Journalling on this platform. It would be probably more helpful if you check WRKJRN and WRKJRNA. Maybe see also RUNSQLSTM for bulk-INSERTs. – PoC Feb 7 '19 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.