Justin Thomas 2 Posted April 3, 2023 Share Posted April 3, 2023 Is there a way to access the records of a hold file on the reporting server (either .foc or .ftm) to insert into a db2 or sql table using passthru? Link to comment Share on other sites More sharing options...
David Beagan Posted April 3, 2023 Share Posted April 3, 2023 You could do something like this:TABLE FILE focus_hold_filePRINT *ON TABLE HOLD AS DB2_TA LE FORMAT DB2END Link to comment Share on other sites More sharing options...
Justin Thomas 2 Posted April 3, 2023 Author Share Posted April 3, 2023 We generally use syntax similar to that (along with DROP) to write hold files to DB2 when we need to. I would like to be insert records from the hold file into the DB2 without modifying existing rows. As a workaround, I have written new records to another DB2 temp table and then used insert in an sql passthru. However, I would like to eliminate that step, and just insert directly from the hold file. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted April 3, 2023 Share Posted April 3, 2023 You can also use the WebFOCUS MODIFY featureHere a refence guide about MODIFY feature : TIBCO FOCUS® Maintaining Databases Release 8207.27.0 and Higher Link to comment Share on other sites More sharing options...
Justin Thomas 2 Posted April 3, 2023 Author Share Posted April 3, 2023 I have only used MODIFY a couple of times, and wasn't sure if it would work between files and tables. Thanks Martin! Link to comment Share on other sites More sharing options...
David Beagan Posted April 4, 2023 Share Posted April 4, 2023 I created a table in SQL Server using SQL passthru:-SET &Engine = 'MSODBC';-SET &Connection = 'mss2022'; ENGINE &Engine SET DEFAULT_CONNECTION &Connection SQL &Engine CREATE TABLE ggsql ( SEQ_NO INTEGER , REGION CHAR(11) , ST CHAR(02) , CITY CHAR(20) , CATEGORY CHAR(11) , UNITS INTEGER , DOLLARS INTEGER ); ENDThen I used the ggsales FOCUS data table to insert rows into the table I just created:-SET &Engine = 'MSODBC';-SET &Connection = 'mss2022'; ENGINE &Engine SET DEFAULT_CONNECTION &Connection -REPEAT ENDREPEAT 20 TIMES-READFILE GGSALES SQL &Engine INSERT INTO ggsql ( SEQ_NO, REGION, ST, CITY, CATEGORY, UNITS, DOLLARS) VALUES (&SEQ_NO,'®ION','&ST','&CITY','&CATEGORY',&UNITS,&DOLLARS); END-ENDREPEATSince it uses SQL Passthru there is no need to have metadata for the ggsql table. If you have metadata for the table you are inserting rows into, then you could use MODIFY FILE as an alternative. Link to comment Share on other sites More sharing options...
David Beagan Posted April 4, 2023 Share Posted April 4, 2023 I was able to create a synonym for the SQL Server table and then create a hold file and use MODIFY FILE to load it into the SQL Server table. CREATE SYNONYM ggsql FOR dbo.ggsql DBMS MSODBC END TABLE FILE ggsales PRINT SEQ_NO REGION ST CITY CATEGORY UNITS DOLLARS ON TABLE HOLD AS ggdata FORMAT ALPHA WHERE RECORDLIMIT IS 20 END MODIFY FILE ggsql SQL SET LOADONLY FIXFORM FROM ggdata MATCH SEQ_NO ON MATCH REJECT ON NOMATCH INCLUDE DATA ON ggdata END Hopefully this gives you another option. Link to comment Share on other sites More sharing options...
Justin Thomas 2 Posted April 4, 2023 Author Share Posted April 4, 2023 Thank you, David. Look forward to giving this a try! Link to comment Share on other sites More sharing options...
Justin Thomas 2 Posted April 4, 2023 Author Share Posted April 4, 2023 Thank you, David! We have used -READ to populate parameters with values, but with some struggles. Where can I find out more about -READFILE? Link to comment Share on other sites More sharing options...
David Beagan Posted April 4, 2023 Share Posted April 4, 2023 Some doc for -READFILE at this link:Developing Reporting Applications, pg 345 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now