can't access db2 table after loading

[rafik2k]

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

[rafik2k]

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

Sorry i forgot to add my requirement:
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

[ray.wurlod]

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.

[rafik2k]

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

[rafik2k]

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

[ray.wurlod]

Don't load junk data?

[rafik2k]

Don't load junk data? ...

Ray,
You are asking or saying?

My source contains around 2 million customer details, out of which 70% records conatins unstructed data/junk character in free format.
No way cleansing this big data.

Thanks

[ray.wurlod]

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.

[DKostelnik]

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 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.