HOLD output (SQLORA) - Creates an Oracle table

Try this:

SET SQLENGINE=SQLORA
SQL SET SERVER CBMLOL
-RUN

SQL SET CONNECTION_ATTRIBUTES CBMLOL/BMLIBI,BMLIBI
-RUN

In the SET CONNECTION_ATTRIBUTES command, one of the values is the User ID, one is the password. If the SET CONNECTION_ATTRIBUTES command is set in edasprof.prf, then I don't think you need it in the fex.

The UserID you're logging-in to the server with requires create table access for the hold file creation to work.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Platinum Member

posted

Thanks Francis. It worked.

Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005

Ignored post by dballest posted

Platinum Member

posted

How do you insert into an existing Oracle table the data that you get when you run the procedure?

Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005

Ignored post by dballest posted

Felix M. Peter

Member

posted

with MODIFY!

-*XYZ is an input-file of any format
TABLE FILE XYZ
PRINT *
BY KEYXYZ
ON TABLE HOLD AS 'ORAIN'
END
-RUN
-*ORAXYZ is an existing Oracle table with existing AFD and MFD
MODIFY FILE ORAXYZ
FIXFORM FROM ORAIN
MATCH KEYXYZ
ON MATCH UPDATE FIELD1 FIELD2 etc.
ON NOMATCH INCLUDE
LOG DUPL MSG OFF
DATA ON ORAIN
END
-RUN

I hope this helps

Felix

5.37 - 7.#
W2K3

Posts: 3 | Registered: October 25, 2006

Ignored post by Felix M. Peter posted

jgelona

Master

posted

If all you are doing is inserting rows, after the MODIFY FILE statement, insert:

SQL SET LOADONLY

This will dramatically improve the performace of the load as MODIFY will not do an initial read to see if the row exists. I remove the ON MATCH UPDATE and after the ON NOMATCH INCLUDE insert a ON MATCH/NOMATCH GOTO TOP.

The other option is to use a mixture of Dialogue Manager and SQL. This is also a lot faster than MODIFY if you have a lot of rows to update or want to update rows based on partial keys and other conditions. For example:

-UPDLOOP
-READ HOLD1 NOCLOSE &CASE.8. &RENEWDT.8.
-IF &IORETURN NE 0 THEN GOTO UPDEND;
-*
 SET SQLENGINE=SQLORA
 SQL SET SERVER &&KIDS_SERVER
 SQL UPDATE TCASE
        SET RENEWAL_DT = TO_DATE('&RENEWDT.EVAL','YYYYMMDD'),
            INTFC_TYP_CDE = 15671
      WHERE CAS_ID = &CASE;
 SQL COMMIT ;
 END
-*
 SET SQLENGINE=SQLORA
 SQL SET SERVER &&KIDS_SERVER
 SQL UPDATE TCL_ADPSUBSIDY
        SET RENEWAL_DT = TO_DATE('&RENEWDT.EVAL','YYYYMMDD'),
            INTFC_TYP_CDE = 15671
      WHERE (CAS_ID = &CASE)
        AND (STRT_DT <= TO_DATE('&RENEWDT.EVAL','YYYYMMDD'))
        AND (END_DT >= TO_DATE('&RENEWDT.EVAL','YYYYMMDD'))
        AND (SUBSDY_DETL_TYP_CD IN (2193,2195,7238))
        AND (APRV_DT IS NOT NULL);
 SQL COMMIT ;
 END
-RUN
-GOTO UPDLOOP
-*
-UPDEND
-CLOSE HOLD1

In the first update, the key is CAS_ID.

In the second, the key is CAS_ID, CL_ID and SEQ_NO. Here I needed to update renewal date for all rows for a given CAS_ID where the other conditions were met.

Just some other options.

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted

Platinum Member

posted

Thanks for your suggestions. I'll give them a try.

Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005

Ignored post by dballest posted

Platinum Member

posted

How do I select which schema to use? We have a database with multiple schema. However, when you define a adapter, you only get to use 1 (depending on the usr/pwd). Currently, we ready have an adapter existing for that database for a particular schema.

Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005

Ignored post by dballest posted

Felix M. Peter

Member

posted

I had the same problem to solve and found the following trick: per adapter you may define several connections but only one per TNS name (we are working with security on on the WebFOCUS server and explicit security for the Orcale adapter).
Now the trick works as follows: per schema I defined a TNS name for the Oracle client used by WebFOCUS in file tnsnames.ora (for simplicity I used the schema names)
and afterwards I created the respective connections per schema/TNS name for the adapter.
In the fex I select simply the the oracle schema to use by selecting the respective connection:

ENGINE SQLORA SET DEFAULT_CONNECTION schemaxyz

make a test!

best regards

Felix

5.37 - 7.#
W2K3

Posts: 3 | Registered: October 25, 2006

Ignored post by Felix M. Peter posted

Platinum Member

posted

Felix,

You can also explicitly get to the schema by the login username/password.

We defined an Oracle adapter using user1/pwd1 to connect to a DBNAME1/SCHEMA1.

Now on the FEX, we used:

SQL SET SERVER DBNAME1
-RUN
SQL SET CONNECTION_ATTRIBUTES DBNAME1/user2,pwd2
-RUN

User2 connects to the same DB but a different schema.

Thanks,
Dan

Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005

Ignored post by dballest posted