can't access db2 table after loading
Moderators: chulett, rschirm, roy
can't access db2 table after loading
Hi,
I am getting some peculiar error when i try to access db2 table after loading using odbc stage.
The error like this "operation not allowed for reason code "1" on table"
This happens not for all jobs, only for few jobs.
after checking set integrity on table, i can access the table.
Could you plz explain why this happens?
Thanks in advance
Regards,
Rafiq
I am getting some peculiar error when i try to access db2 table after loading using odbc stage.
The error like this "operation not allowed for reason code "1" on table"
This happens not for all jobs, only for few jobs.
after checking set integrity on table, i can access the table.
Could you plz explain why this happens?
Thanks in advance
Regards,
Rafiq
Re: can't access db2 table after loading
Sorry i forgot to add my requirement:rafik2k wrote:Hi,
I am getting some peculiar error when i try to access db2 table after loading using odbc stage.
The error like this "operation not allowed for reason code "1" on table"
This happens not for all jobs, only for few jobs.
after checking set integrity on table, i can access the table.
Could you plz explain why this happens?
Thanks in advance
Regards,
Rafiq
I have two set of job in my sequence, one main job which load into target and other called audit job which captures source row count and target row count and some more information.
The second job fails in the sequence due to above mentioned problem.
Can u plz suggest us suitable solution.
Regards,
Rafiq
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Get your DBA to trace what is happening. For example, are locks not being released, are indexes being left in an inconsistent state? Use your DB2 documentation to learn what "reason code 1" is. Do you have SELECT as well as INSERT privilege on the table in question? Are you naming the correct table in the second job? All of the above are possibilities - you (with the help of your DBA) must be the detective.
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.
Thanks Ray,
I will check with DBA and will know what is the issue.
For the time being i am calling db2 set integrity check sql statement after job subroutine in every job and working fine.
For every job i have to do apply this logic. which is time consuming task for all the job.
Is any other better way to do the same.
Regards,
Rafiq
I will check with DBA and will know what is the issue.
For the time being i am calling db2 set integrity check sql statement after job subroutine in every job and working fine.
For every job i have to do apply this logic. which is time consuming task for all the job.
Is any other better way to do the same.
Regards,
Rafiq
Hi,
As per DBA this problem comes due to loading junk data into table.He suggested to use set integrity command.
I see don't have this issue for job where data size is small.
For big data size job, I see this issue.
Anyway using above command things are going fine.
But very time consuming task for all the job. For this i had to write batch file.
Please suggest me if anybody of you have any other solution for this?
Thanks
As per DBA this problem comes due to loading junk data into table.He suggested to use set integrity command.
I see don't have this issue for job where data size is small.
For big data size job, I see this issue.
Anyway using above command things are going fine.
But very time consuming task for all the job. For this i had to write batch file.
Please suggest me if anybody of you have any other solution for this?
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Why ever not? Data cleansing is a large part of ETL. Even if you want to store "junk" data, there are no restrictions on what you can store in VarChar columns. So maybe a rethink of the target tables' data types may be in order?
Take a look at QualityStage - it is particularly good at extracting buried data from free format fields using pattern matching and probability-based techniques.
Take a look at QualityStage - it is particularly good at extracting buried data from free format fields using pattern matching and probability-based techniques.
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.
-
- Participant
- Posts: 34
- Joined: Tue Jan 30, 2007 6:13 pm
- Location: Central Florida
Re: can't access db2 table after loading
I don't know if anyone ever answered this question or not. You have to run a set integrity command because there is a constraint on the table you are loading. It could be something as simple as a Foreign Key or as obscure as the table is used in a view. In any event, there is a constraint on the table that needs to be validated and the only way that happens is when you run a "set integrity" command.rafik2k wrote:rafik2k wrote:Hi,
I am getting some peculiar error when i try to access db2 table after loading using odbc stage.
The error like this "operation not allowed for reason code "1" on table"
This happens not for all jobs, only for few jobs.
after checking set integrity on table, i can access the table.
Could you plz explain why this happens?
Thanks in advance
Regards,
Rafiq
Doug
AAA Auto Club Group
Listen to:
Porcupine Tree
Nosound
Days Between Stations
AAA Auto Club Group
Listen to:
Porcupine Tree
Nosound
Days Between Stations