SQL insert into 2 libraries with 1 query

[techas400]

I have the following query in SQLRPGLE for DB2:

  INSERT INTO ITEMS2 (PROGRAM, VLDFILE, VLDFLD, 
    SELFILE, SELFLD) VALUES(:SCAPP   , 'CSTMR', 'CYC',
    'BYC', 'BYCC');

I would like this query to be run in 2 libraries as in FIRST/ITEMS2 and SECOND/ITEMS2

where FIRST and SECOND are the library names. Can this be achieved in one query?

[DeadManWalks]

Re: SQL insert into 2 libraries with 1 query

1) issue a chgcurlib before you run it each time. Change it back to *CrtDft when done.
2) use dynamic , ie build the insert as a string.

[techas400]

Re: SQL insert into 2 libraries with 1 query

Hi DeadManWalks,

How could I achieve that in a SQLRPGLE. Could you please provide an example of the same? Also how should I move the insert to a string?

Please guide.

Thanks.

[DeadManWalks]

Re: SQL insert into 2 libraries with 1 query

This works as the CurLib is searched first.

Roughly i do;

Command = 'CHGCURLIB CURLIB(' + %Trim(MyDataLIbrary) + ')';
QCmdExc(Command: %Len(%TrimR(Command)));

SQL Here

Command = 'CHGCURLIB CURLIB(' + %Trim(MyDataLIbrary) + ')';
QCmdExc(Command: %Len(%TrimR(Command)));

SQL Here

then set it back
Command = 'CHGCURLIB CURLIB(*CRTDFT)';
QCmdExc(Command: %Len(%TrimR(Command)));

I'd do this over the building of the SQL string.
MySqlString = 'insert into ' + MyLib + '/table......' Where you use ':var' you would use ? and load those later. There are examples to do this but I would stick with setting the libs.

[Scott Klement]

Re: SQL insert into 2 libraries with 1 query

Personally, I change my CURLIB all the time when I'm working. I often do this to manipulate my enviroment to get test files, live files, etc. If someone were to write a program that changed it and left me at *CRTDFT after the progam was done, I'd be most upset. Programs shouldn't be changing my curlib or my library list!

Instead, consider creating an ALIAS. First create it for one library, then change it to another library. In both cases, have the INSERT target the alias...

Or, just hard-code the library into the INSERT. Even that would be better than changing curlib or libl in your program!

[B.Hauser]

Re: SQL insert into 2 libraries with 1 query

Instead of changing the current library (within the library list) you may use the SQL command SET CURRENT SCHEMA.
After this SQL command is executed, all unqualifed specified physical files/tables or views are searched within this schema, at least for SQL statements executed with SQL naming conventions and dynamic SQL statements when using SQL naming conventions.
The current schema does not need to be within the library list.
... but when using this technique you need to reset the current schema before exeuting other SQL statements.

/Free
 For Index = 1 to 3;
     Select;
     When Index = 1;
          Exec SQL  SET CURRENT SCHEMA = 'FIRST';
     When Index = 2;
          Exec SQL  SET CURRENT SCHEMA = 'SECOND';
     Other;
          Exec SQL  SET CURRENT SCHEMA Default;
´         Leave;
     EndSL;

     Exec SQL Insert Into TableX
                     Values(...);

 EndIF;

An other way would be executing the OVRDBF CL command.

[techas400]

Re: SQL insert into 2 libraries with 1 query

Hi Hauser,

I tried using the set current schema technique. But when my program tries to insert into the same file for library "Second" it throws, Duplicate key value found speciefied in joblog. It clearly shows that the library was not changed from First to Second while inserting the same set of records there. Also the file in First was rightly populated but Second continued to remain empty with Duplicate Value error.

Please guide on how exactly to set/reset these lib values.

Thanks.

[chrisr]

Re: SQL insert into 2 libraries with 1 query

Hi Hauser,

I tried using the set current schema technique. But when my program tries to insert into the same file for library "Second" it throws, Duplicate key value found speciefied in joblog. It clearly shows that the library was not changed from First to Second while inserting the same set of records there. Also the file in First was rightly populated but Second continued to remain empty with Duplicate Value error.

Please guide on how exactly to set/reset these lib values.

Thanks.

I can't see why Birgitta's example wouldn't work for you. Can you post your code?