June 24, 2013, 08:59 AM
John C.DB2 Syntax Question
Good Morning,
We are using the following syntax for DB2 hold files, however once in we cannot open the .fex back into the GUI. Not too big of a deal, just very annoying. Is there any other syntax we can use?
ON TABLE HOLD AS APP/MASNAME FORMAT DB2 TABLENAME AS400LIBRARY/DB2TABLENAME
Thank you!
John C.
June 26, 2013, 04:19 AM
GamPMaybe just code it as the GUI would...
ON TABLE HOLD AS DB2TABLENAME FORMAT DB2
The sql connection that is active for DB2 will define what database to use.
June 26, 2013, 08:29 AM
John C.If we are working in our production library, then want to save a file in another library. Is there a syntax to use? Possibly a set value? I haven't been able to find anything on the topic searching the documentation and previous posts here.
Our production files are in Library A, however when we save files we only have authoirty over Library B.
June 27, 2013, 11:13 AM
drbrownWe have the same scenario. We just created two different synonyms, one that points to LIB_A and one that points to LIB_B. The other way we've done this is to create an AS/400 stored procedure where we pass the library we want to use and the dataset we want and it does the insert/update to the appropriate table.
July 03, 2013, 07:44 AM
GamPYou could read the data with one db2 connection active, hold the data in a temporary intermediate file, change the db2 connection, read the intermediate file and hold it as DB2 in the new connection.
July 03, 2013, 08:16 AM
John C.quote:
You could read the data with one db2 connection active, hold the data in a temporary intermediate file, change the db2 connection, read the intermediate file and hold it as DB2 in the new connection.
GamP
GamP,
Do you happen to have an example with CARS? Would like to see how this is done. Appreciate the feedback!
JC
July 03, 2013, 08:17 AM
John C.quote:
Originally posted by drbrown:
We have the same scenario. We just created two different synonyms, one that points to LIB_A and one that points to LIB_B. The other way we've done this is to create an AS/400 stored procedure where we pass the library we want to use and the dataset we want and it does the insert/update to the appropriate table.
drbrown,
Do you also have an example? Would like to see how your doing this on the WebFOCUS side.
Thank you!
JC
July 08, 2013, 05:29 AM
GamPExample ... no, I have none at hand.
But it would be something like:
SQL SQLMSS SET DEFAULT_CONNECTION READ_DB
END
TABLE FILE WHATEVER
PRINT *
ON TABLE HOLD AS INBETWEEN FORMAT ALPHA
END
-RUN
SQL SQLMSS SET DEFAULT_CONNECTION WRITE_DB
END
TABLE FILE INBETWEEN
PRINT *
ON TABLE HOLD AS TABLENAME FORMAT MSSQL
END
Haven't tested this and I don't have DB2 nor AS400 to really help you.
July 09, 2013, 04:22 PM
drbrownThe basic premise for writing to a DB2 table as a synonym is this:
SET ASNAMES = ON
TABLE FILE TABLE1
FIELD1 AS 'DB2FIELDNAME2'
FIELD1 AS 'DB2FIELDNAME2'
ETC...
ON TABLE HOLD AS TEST
END
MODIFY FILE DB2TABLE
FIXFORM FROM TEST
DATA ON TEST
END
For the DB2 stored procedure method, you create your stored procedure on the AS/400, and then you can create a synonym in WebFocus over that stored procedure. Then you do a join from your hold table in Webfocus to the stored procedure synonym and it will call the stored procedure for every record in the hold table. The beauty is that the stored procedure can be RPG, CL, SQL, REXX, Java etc. We have a lot of RPGLE stored procedures and the performance is amazingly fast. I can't show the exact code here, but if you need help with either method or creating stored procedures, let me know. I'd be happy to help.