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.

Maybe 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.

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.

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.

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.

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

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

Example ... 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.

The 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.