Generate report for Server Job

ketfos · Post by **ketfos** » Sun Dec 25, 2011 9:07 pm

I have generated a report for a server job.
It is three stages - Source is hash file - transformer - target is Oracle OCI stage.
My action code in Oracle OCI is
Update exisitng rows or insert new rows/

When I view the report, I see the following statements under Oracle OCI stage

1. User defind SQL
update....
insert ....

2. User Defined Create table
Create table....

3. User Defined Drop table.
Drop table ...

My question is that when I view the Oracle OCI stage, I don to see any Create table/Drop table SQL. I only see the User defined SQL (update..../Insert statements)

Any ideas to find why drop and create statements are generated.

ketfos

pandeesh · Post by **pandeesh** » Sun Dec 25, 2011 9:54 pm

May i know how you are generating the report.
Using command line option or GUI?
Thanks

ketfos · Post by **ketfos** » Sun Dec 25, 2011 10:30 pm

Using GUI - In designer, click on File Menu and Select Generate option.

pandeesh · Post by **pandeesh** » Sun Dec 25, 2011 10:31 pm

Ah!! you are in 8.x..Ok,Ok.

ketfos · Post by **ketfos** » Sun Dec 25, 2011 10:34 pm

Yes 8.1 Server Edition

pandeesh · Post by **pandeesh** » Sun Dec 25, 2011 10:43 pm

Can you please post the exact create/Drop table sql?
Those might be creation and deletion of temporary table for processing.

ketfos · Post by **ketfos** » Sun Dec 25, 2011 11:40 pm

Oracle OCI : Oracle_OCI_20

Table Name: EXPOSURE
Enable case sensitive table name and column name:
Generate SQL: Yes
User-defined SQL: UPDATE EXPOSURE ..............
Pre 4.2 User-defined SQL Behavior: Yes
Array Size: 2000
Rows per transaction: 0
Create target table: No
Generate CREATE TABLE: Yes
User-defined CREATE TABLE: CREATE TABLE EXPOSURE ..........
Drop target table: No
Generate DROP TABLE: Yes
User-defined DROP TABLE: DROP TABLE EXPOSURE
Clear table: No
Update action: Update existing rows or insert new ones
Treat warning as fatal:
Transaction isolation level: Read Committed

pandeesh · Post by **pandeesh** » Sun Dec 25, 2011 11:46 pm

Sorry ..honestly I don't have any clue about that.
Don"t worry ..someone will help you .
May be Ray or Craig.. :D

ray.wurlod · Post by **ray.wurlod** » Mon Dec 26, 2011 4:35 am

The DML statements are present in case you ever decide to enable the "create table" property possibly with the "drop table" sub-property. There's nothing there to be concerned about.

chulett · Post by **chulett** » Mon Dec 26, 2011 8:06 am

Exactly, standard stage stuff. That option doesn't get a lot of play in my experience as you can't use it to simply create the target table once like you could with say a hashed file. It will happily create it but then fail on every subsequent run as the object 'already exists'. Use it if you have a table you need to drop and recreate on every run.