Hi,
I am having a job which is running for multiple plans. In this process we need to create temporary tables for each plan. Based on the values on temporary tables we need to update a lookup table.
here my problem is
1) How to create temporary tables based on plans, because plans are not fixed. At certain instance we may run jobs for single plan and at another instance we may run for multiple plans. (Here plan means health plan what we have).
2) If we are updating a single lookup table based on temporary tables, are there any locks can happen? If not how all jobs share a single lookup table?
I know that all these issues we can learn only by trial and error. But, I dont have access to the parallel environment by this time. I need to document these issues before I design jobs.
Thanks in advance.
Kavuri
Multiple instances accessing a single table
Moderators: chulett, rschirm, roy
You'll need a parameter to be used in the name of any temporary tables as well as the SQL in the jobs that create/access said temporary tables.
Locking will happen if you're writing to the same row in the target table. You didn't state your database and thus the load method used for each database has its particulars. If DIRECT path loading in Oracle you can't have two simultaneous loads or even one connection SELECTing and other loading. If you're using DML based loading you're much better off dealing with concurrent loads.
Locking will happen if you're writing to the same row in the target table. You didn't state your database and thus the load method used for each database has its particulars. If DIRECT path loading in Oracle you can't have two simultaneous loads or even one connection SELECTing and other loading. If you're using DML based loading you're much better off dealing with concurrent loads.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
1) I need a parameter i.e Is this to represent a table name? If so suppose if I give the paramemter name as "tmp_new_table", then the temporary tables should be something like "tmp_new_table1", "tmp_new_table2".....
That too these tables all are need to be created on the fly but with same fields. Can you through some more light on this.
2) I am using DB2 database. Updating the table in the sence what I mean is to add new rows, based on the values in temporary tables. So sometimes multiple temporary tables may have same value for the field which is to be loaded. In that case as I had declared the field as PK, what happens?
Ex : suppose my field name in lookup table is "member_id".
If all the temp tables also have member_id as field and I am loading lookup table with values in temp tables.
Then suppose temp_table1 contains a value 123 and temp_table2 contains a value 123. and both are trying to insert the same value into the lookup table, what happens?
That too these tables all are need to be created on the fly but with same fields. Can you through some more light on this.
2) I am using DB2 database. Updating the table in the sence what I mean is to add new rows, based on the values in temporary tables. So sometimes multiple temporary tables may have same value for the field which is to be loaded. In that case as I had declared the field as PK, what happens?
Ex : suppose my field name in lookup table is "member_id".
If all the temp tables also have member_id as field and I am loading lookup table with values in temp tables.
Then suppose temp_table1 contains a value 123 and temp_table2 contains a value 123. and both are trying to insert the same value into the lookup table, what happens?
A true "temp" table doesn't need anything to uniquely identify it - it's local to the connection. However, you're probably needing it to last thru multiple steps which has a different connection for each. Therefore, your "temp" tables are physical tables you'll have to create and drop and need a unique name.
As for two independent processes creating/updating the same row, you're going to have to decide how you wish precedence to happen. One process will lock out the other, but nothing prevents one from not doing the update afterwards. You're issue is not one of locking, but of business rules clarification. Then, you'll probably have to disallow concurrent processing as you won't be able to have the same row being manipulated and deal with precedence.
As for two independent processes creating/updating the same row, you're going to have to decide how you wish precedence to happen. One process will lock out the other, but nothing prevents one from not doing the update afterwards. You're issue is not one of locking, but of business rules clarification. Then, you'll probably have to disallow concurrent processing as you won't be able to have the same row being manipulated and deal with precedence.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle