capture insert statements while migrating data into table
Moderators: chulett, rschirm, roy
Not aware of any way to accomplish getting this information automatically added to the job's log. What stage are you using?
Last edited by chulett on Fri Oct 17, 2014 11:34 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
- 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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
hi ,
my datastage load job :
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.
my datastage load job :
Code: Select all
dataset[src] ---Xfm stage-----lk_ld--->oracle connector[destination]
|
lk_sqlfl_write
|
v
sequential file stage
'INSERT INTO EMPLOYEE ( E_ID,E_NAME) VALUES( ' : E_ID :',' : E_NAME :');'
by using this i will capture sql insert statements into sequential file.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.