Tag Archives: DB2

DB2 Errors and How to Solve them

1.SQL0668N Operation not allowed for reason code “3” on table

“tablename”. SQLSTATE=57016

Solution:

db2 load query table tablename

 

2. SQL3501W The table space(s) in which the table resides will not be placed in

backup pending state since forward recovery is disabled for the database.

Tablestate:

Load Pending This table is pending

Solution:

Run: db2 load from 1.ixf of ixf terminate into swt_his_tran_log

 

3. SQL0668N Operation not allowed for reason code “1” on table

“tablename”. SQLSTATE=57016

Solution: db2 set integrity for tablename immediate checked

 

4. SQL0668N Operation not allowed for reason code “7” on table

“tablename”. SQLSTATE=57016

Solution.

Do the table of reorg ：db2 “reorg table tablename”

 

5. db2 connect to ibank

SQL1015N The database is in an inconsistent state. SQLSTATE=55025

Solution:

db2 restart db ibank

 

6.SQL0290N Table space access is not allowed. SQLSTATE=55039

db2 list tablespaces show detail

Status is 0x0020

Solution.

Online backup tablespace operation

db2 backup database jxcx926 user db2inst1 using db2inst1 tablespace TBS_32K online to /home/db2inst1

 

7.When starting the cluster database, it reports an error that some nodes failed to start

Solution: Modify db2diag log level: db2 update dbm cfg using diaglevel 4 immediate

View the cause of the problem from the detailed log

The original is 3, after modifying it to 4, I can see the detailed information and found that it is a shared memory problem

Release the shared memory: ipclean and restart the database

 

8. db2advis execution failed

Solution.

Execute db2 -tvf EXPLAIN.DDL in /sqllib/misc directory

 

9.When there is an error code about SQLSTATE in the database error report, you want to see the specific information

You can execute db2 ?sqlstate (db2 ?55039) on the database

 

Creating users with DB2, solving the problem of sqlstate=58004)

It looks like DB2 and OS user accounts are associated, so let’s learn how to create a windows account first

 

C:\Documents and Settings\Administrator>net user

User accounts for \\IBM-R8M6A58

——————————————————————————-
ASPNET cyper.yin db2admin
Guest HelpAssistant SUPPORT_388945a0
The command completed successfully.

Add a user named test

D:\>net user test passw0rd /add
The command completed successfully.

View all user groups

D:\>net localgroup

Aliases for \\IBM-R8M6A58

————————————————-
*Administrators
*Backup Operators
*DB2ADMNS
*DB2USERS
*Guests
*HelpServicesGroup
*Network Configuration Operators
*ora_dba
*Power Users
*Remote Desktop Users
*Replicator
*Users
The command completed successfully.

To add the user test to the administrators group.

D:\>net localgroup administrators test /add
The command completed successfully.

At this point, user test is now able to connect to the sample database.

It should be enough to add test to the db2admns group. I suddenly tried to use the db2cc command and it didn’t work.

I think it is possible that the “typical installation” does not have this feature, so I double-click the unpacked setup.exe to “use existing products”, “modify” mode to reinstall

As a result, after the installation, the scene happened again

C:\Documents and Settings\Administrator>db2start
DB2 : The service has returned a service-specific error code.

SQL1042C An unexpected system error has occurred. SQLSTATE=58004

Following the clues here
：
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14906211&tstart=0

Re: DB2 ExpressC v.10.1 doesn’t work on Windows xp 32bit (?)
Method 1: We resolved this problem by adding c:\program files\ibm\gsk8\lib to the PATH environment variable.

Method 2: Whenever you type the command db2start if the following error is displayed
DB2 : The service has returned a service-specific error code.
SQL1042C An unexpected system error occurred. SQLSTATE=58004
Create icc64 directory in C:\Program Files\IBM\SQLLIB\BIN\ and copy the contents of C:\Program Files\IBM\gsk8\lib64 to C:\Program Files\IBM\SQLLIB\BIN\icc64 directory (copy only the contents of lib64 directory and also make sure you copy all the contents of lib64 directory)
Option 2 is talking about the 64-bit case, my computer is 32-bit, I copied
C:\Program Files\IBM\gsk8\lib directory all the contents to D:\IBM\SQLLIB\BIN\icc directory, also succeeded! (icc directory does not exist, you need to create it yourself)

DB2 Database table operation error SQL0668N Operation not allowed for reason code “1” on table “db”. S…

Error SQL

Operation not allowed for reason code “1” on table “MARKET.PURE_ USER”.. SQLC

//run sql
select * from PURE_USER

There may be one or more rows that violate the
constraint on the data definition. This table cannot be used for operations. If the subordinate table is in the check pending state, the operation on the parent table that is not in the check pending
State may also receive this error

user response: execute the set integrity
statement with the immediate checked option, and ensure that the data meets all the constraints defined on the table or its subordinate tables

//solution:
set integrity for dbname immediate checked

Any operation on the table is not allowed, and sqlstate = 57016, sqlcode = – 668 is prompted. Error of reason code “7”: sql0668n operation not allowed for reason code “7” on table XXX

//run 
CALL SYSPROC.ADMIN_CMD('reorg table dbname')