CODEWITHSUNDEEP
selectdb2
deltascience
deltascience

Reputation: 3380

Error while doing a select on all tables in DB2

I'm working on DB2 database. When I run a simple select on a table I get this error:

DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=1;My_Table, DRIVER=4.18.60

I looked on the internet and some says that executing this command can solve the problem :

CALL SYSPROC.ADMIN_CMD('REORG TABLE My_Table')

When I execute the previous SQL command I get this error: 

[Error Code: -2216, SQL State: 01H52]  DB2 SQL Error: SQLCODE=-2216, SQLSTATE=01H52, SQLERRMC=-668, DRIVER=4.18.60

How can I solve this issue ?

Many thanks.

Upvotes: 1

Views: 18096

Answers (2)

Ponmathavan Mani
Ponmathavan Mani

Reputation: 31

CALL SYSPROC.ADMIN_CMD('REORG TABLE My_Table')

that code Not work for me.So i try and get another solution.

SET INTEGRITY FOR table_name IMMEDIATE CHECKED

Upvotes: 3

bhamby
bhamby

Reputation: 15450

I'll start at the end. Your second error is telling you that you're running into the same error when trying to run a REORG. If you look at the documentation for -2216, you see this:

SQL2216N

SQL error sqlcode occurred while reorganizing a database table or its indexes.

Explanation

An error occurred during the reorganization of a database table or its indexes.

The utility stops processing.

User response

Look at the SQLCODE (message number) in the message for more information. Make changes and resubmit the command.

So, let's look back at your second error. If you'll notice in the documentation, sqlcode is italicized, which means that will show up in the messages contained in SQLERRMC. Looking at that, it's another -668.

If you look at the documentation for -668, you will find your answer. Again, looking at SQLERRMC, the reason-code for the error is 1, so we look down at what to do for reason code 1, and we get the following:

SQL0668N

Operation not allowed for reason code reason-code on table table-name.

Explanation

Access to table table-name is restricted. The cause is based on the following reason codes

reason-code:

1

The table is in the Set Integrity Pending No Access state. The integrity of the table is not enforced and the content of the table may be invalid. An operation on a parent table or an underlying table that is not in the Set Integrity Pending No Access state may also receive this error if a dependent table is in the Set Integrity Pending No Access state.

....

User response

1

Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option on table table-name to bring the table out of the Set Integrity Pending No Access state. For a user maintained materialized query table, execute the statement with the IMMEDIATE UNCHECKED option instead of the IMMEDIATE CHECKED option.

Upvotes: 3

Related Questions