We help IT Professionals succeed at work.

How to remove the table from set integrity pending state in db2 after load

partminer
partminer used Ask the Experts™
on
Hi,

When I try to load the data into TableA using the below syntax

load from "H:\tab282.ixf" of ixf  modified by generatedoverride insert into TableA nonrecoverable" it failed with the following error.

SQL2038N .A database system error "SQLUIIGN" occured during processing.
SQL0289N unable to locate new pages in table space "TABLESPACE_TS"
SQLSTATE=57011
After that I try to run the set integrity command but i am getting another error.
---
set integrity for tableA immediate checked;
--
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0668N  Operation not allowed for reason code "3" on table
"tableA".  SQLSTATE=57016

SQL0668N  Operation not allowed for reason code "3" on table "TABLEA                                        ".

1.Can you please help me how to come out of above situation.
2.If I am not sure of the exact problem how to find out effected tables after load operation or set integrity pending state tables?

Thank in  advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
SQL0668N Reason Code 3 indicates that the Table is in a LOAD PENDING state.  This means a previous load command failed to complete.  This condition will need to be cleared first using the TERMINATE option of the load command:

load from "H:\tab282.ixf" of ixf  modified by generatedoverride TERMINATE into TableA nonrecoverable


Once that is accomplished, you can go back to the original error - SQL0289N.  This indicates that the  "TABLESPACE_TS" tablespace where TableA resides has no more space available to hold the data being loaded into it.  

You didn't indicate if this is a DMS or an SMS tablespace.  If it's SMS, then you need to add another Path or expand the drive/path where this tablespace resides so that the tablespace can grow.  If it's DMS, then you need to expand the size of the tablespace - assuming there is disk space left where the existing container(s) are located.  

If you need assistance determining how to expand the tablespace, talk to your DBA - or if you are the DBA, then post again here with the tablespace definition so I can help you further.

Greg

Author

Commented:
very urgent , can you please let me how can I clear the transaction log for the db2 database installed on windows.there is no space in the database?

Author

Commented:
Can you please let me know how to take dummy backup in windows.I use /dev/null for linux.
Commented:
Sounds like you found the root cause of the space issue...
You can run
 db2 get db cfg
For the name of the FIRST ACTIVE LOG file and the location of the LOG Files (LOGPATH).  If there are any log files PRIOR to the first active log in the LOGPATH, these can be archived/deleted.  However, if the oldest log file there is the FIRST ACTIVE LOG, you will need to find a way to create more space on that device.  

You can try forcing off all connections and recycling the database to see if that helps to free up the logs.  

Greg

Commented:
I believe it should be:

backup DB dbname to NUL

Greg

Author

Commented:
I have executed the below command

db2 backup db testdb nul
I am getting following error.

SQL30082N -security processing failed with error "3"(password missing).
Kent OlsenData Warehouse / Database Architect

Commented:
Hi part,

Run the same command from the DB2 instance administrator account.


Kent
Commented:
backup DB dbname USER username USING password to NUL

where USER is an id with at least DBADM rights

Author

Commented:
Dear Greg, Can you please let me know how to archive the old log files.

Author

Commented:
Hi, I am getting database is curretly in use message.How can I terminate the connected applications in windows ?Can you please let me know.

Author

Commented:
How can I get rid of the below error.Please help me I am in big trouble.

connect to TESTDB user XXXX using      
SQL1116N  A connection to or activation of database "TESTDB" cannot be made
because of BACKUP PENDING.  SQLSTATE=57019

SQL1116N  A connection to or activation of database "TESTDB                                                                " cannot be made because of BACKUP PENDING.

Explanation:

The specified database requires a backup to be taken to provide a
starting point for roll-forward recovery.  

 No connection was made.  

User Response:

Back up the database by calling the BACKUP routine or, if
roll-recovery is not required, set the database configuration
parameters LOGARCHMETH1 and LOGARCHMETH2 to OFF.  

 sqlcode :  -1116

 sqlstate :  57019
Kent OlsenData Warehouse / Database Architect
Commented:
hi part,

There are a couple of things you can do, but it's probably your own connection that is active.

run:

  db2 terminate


Then try again.  If that does work, try running DB2 FORCE APPLICATION ALL


Kent

Author

Commented:
Hi, I have taken the backup successfully using 'backup DB dbname USER username USING password to NUL '.

Can you please let me know the following.

1.Can we load the data into a table which has generated (no identity) datatype can be loaded using import option with no issues?
2.If we terminate the load process , does it impact the performance or database system?
3.What do you prefer to load data. Import or load?

Appreciate any help .Thanks so much.

Author

Commented:
Hi,

Even I took backup also transaction log file system (87%) did not cleared.Can you please let me know how to clean transaction log files?Please help me....
Commented:
The number of Log files are defined in the db cfg - LOGPRIMARY.  This tells you how many log files will be kept active at all times.  LOGFILESZ will tell you how big each file will be.  These 2 together tell you how much space AT A MINIMUM will be consumed when the database is active.  The only way to "shrink" this minimum is to define a smaller LOGPRIMARY, LOGFILESX or both - and then recycle the database.

Kent OlsenData Warehouse / Database Architect
Commented:
Hi part,

The backup file was written to the null device.  i.e. you don't have one.  The successful backup operation cleared the flags that were causing other issues.

Do you have a backup that you want to restore?  If so, restore it using the RESTORE DATABASE command.

The BACKUP command is normally paired with the RESTORE command.  Likewise EXPORT and IMPORT.  If you created your backup with the BACKUP command, use RESTORE to put it back.

For now, don't worry about the transaction log size.  The backup process doesn't affect the size of the log file.


Kent

Author

Commented:
Dear Kent/Greg,

Big thanks for all your unmeasurable help.

Can you please let me know the following.
1.I have a table that has one column defined as 'GENERATED as UCASE(xx) and GENERATED ALWAYS as DATE(xxx) '(No-identity).If I just load the data with the simple import option as below
import from \path\file.ixf of ixf insert to <tablename>
Does the table is loaded with no issues and next time when we insert the data into the tables, generated columns preserve the generated definitions?

2.If we terminate the load process , does it impact the performance or database system?

3.What do you prefer to load data. Import or load?What do you suggest?

Waiting for your response!Thanks.
Kent OlsenData Warehouse / Database Architect
Commented:
Hi part,

1.  The IMPORT process won't change the table structure, definition, or control.  After an IMPORT the GENERATED clauses are still in effect so new rows will get the data values defined by the column controls.

2.  Terminating a LOAD and terminating an IMPORT can have different results.  You saw the 'Load Pending' status on the table that was probably caused by a terminated load.  If you're loading the table with logging turned off, the table will likely be considered corrupt and inaccessible.  I've never seen a terminated IMPORT cause an issue, but I'm sure that it can.

3.  LOAD and IMPORT have subtle differences.  LOAD is generally faster, especially if you can do a LOAD FROM CURSOR.  IMPORT is more flexible and can do a few more things.  If you've got a lot of data use LOAD FROM CURSOR if you can.


Kent

Author

Commented:
Hi Kent,

1.Does it mean, no extra care to be taken when we use load the tables that has generated datatypes(no-identity), using import option.Automatically import would preserve the column datatype definition.
Kent OlsenData Warehouse / Database Architect
Commented:
Oops.  :)

The IMPORT should follow the table rules.  If the data that you're trying to import violates any of the rules of the table (i.e. the GENERATE ALWAYS clause) the row will be rejected.

This is true for LOAD, too.


Kent

Author

Commented:
Great thanks to Db2 gurus.