capture insert statements while migrating data into table

chulett · Post by **chulett** » Fri Oct 17, 2014 8:13 am

Not aware of any way to accomplish getting this information automatically added to the job's log. What stage are you using?

qt_ky · Post by **qt_ky** » Fri Oct 17, 2014 9:39 am

I think it can be done in this way:

- Ensuring database logging is at the level you need to capture all the SQL
- Ensuring database privileges allow you to query the database log/audit tables
- Create an extract job to query the database log/audit tables

Exact details will vary with each database system.

Within DataStage itself, it may be possible to capture through tracing settings or debug settings. There are some connector env vars starting with "CC" which, if set, may possibly force such details into a job log. I expect that this approach, if it works, would significantly impact your job performance.

chulett · Post by **chulett** » Fri Oct 17, 2014 9:56 am

Emphasis on significantly.

karteek · Post by **karteek** » Fri Oct 17, 2014 9:56 am

hi ,

my datastage load job :

Code: Select all

dataset[src] ---Xfm stage-----lk_ld--->oracle connector[destination]
                         |
                         lk_sqlfl_write
                         |
                         v
                    sequential file stage

in transformere stage for the link lk_sqlfl_write i will harcode :

'INSERT INTO EMPLOYEE ( E_ID,E_NAME) VALUES( ' : E_ID :',' : E_NAME :');'

by using this i will capture sql insert statements into sequential file.

chulett · Post by **chulett** » Fri Oct 17, 2014 9:58 am

Or that.

qt_ky · Post by **qt_ky** » Fri Oct 17, 2014 10:05 am

Each option has pros and cons... That last option creates a potential ongoing maintenance headache and it wouldn't pass any sort of security audit but it would perform well and may give you just what you need.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 17, 2014 4:47 pm

DataStage uses prepared execution. That is, the SQL is sent to the database only once, to be "prepared" (run through query optimizer, etc.). This happens before any rows are processed, and throws metadata mismatch and other warnings/errors if necessary.

As rows are processed, DataStage sends arrays of row values to the database server.

Individual INSERT statements are not executed. Therefore you have to construct an INSERT statement as identified in this thread.