Update a column using OCI stage

[I_Server_Whale]

Hi All,

I'm trying to update a column of a table by running the following query:

Code: Select all

UPDATE LSTG_REJECT_REC
SET TARGET_TABL_NM = decode(TARGET_TABL_NM,'LDW_CERT_%','LDW_CERT','LDW_CERT_END_%','LDW_CERT_END',TARGET_TABL_NM)

My job design to execute this query is as:

Code: Select all

  Transformer(Dummy)------------------------------------>OCI stage

The OCI stage executes the 'user-defined SQL' as shown above. Is this an efficient way of updating the column of a table?

Your thoughts and guidance are very much appreciated,

Thanks,
Naveen.

[kcbland]

Efficient for speed? How are you going to control commit rate or rollback usage? Your design means that only 1 row of data is required for processing in order to fire this query. Which rows are affected? How many rows were actually updated? Why is it taking so long? How will you measure this job?

If none of those questions matter, your design is, IMO, a little hokey. Why build job parts to run a simple SQL script, when a shell script call to sqlplus could run this as a script? Sure would be easier to manage. But if you're told to "do this using DataStage", aren't you cheating? Is DataStage just a surrogate SQL runner or an ETL tool.

Consider first querying the rows that meet your criteria and writing out the primary keys and the replacement value to a file. Then, run that file into a transformer and then to the OCI stage using update existing rows only SQL. Now you get metadata, restartability, commit rate control, performance measurement, audit trails, etc.

But that's just my opinion.

[ogmios]

The original solution works for as long as you don't exhaust transaction log space (because it's a single transaction), this depends on the database settings and width of a single row. Usually this works for upto +- 400.000 rows, not for millions.

Solution is to have actual input on the left hand side of your job, this will allow you to use transactions in a proper way.

Ogmios