SQL error "-964" during "ACCTIT" access and The transaction log for the database is full

[Former Member]

Dear All,

I am doing client import in Quality server and facing some issue. The database is DB2 and OS is HP-UX . I am

getting an error as SQL error "-964" during "ACCTIT" access in the log. and then saying that The transaction log for the database is full.

I have also extended the log size using command db2 UPDATE cfg for QAS using LOGFILSIZ 50000 but still same issue.

then I increase the size to 70000 using command db2 UPDATE cfg for QAS using LOGFILSIZ 70000

but still same issue. I am attaching the log for the same with this. Please update me ASAP.

Regards,

Omkar M.

LOG.txt.zip

[Former Member]

Hii Manu,

I had changed the size of the primary log to 170 and then set everything up for client import but after running import request for 1 and 1/2 days it is giving me the same issue. And also I restarted db2 when increased log size.

Regards,

Omkar M.

[manumohandas82]

Hi Omkar ,

Is there any ways you can increase the filesystem size temporarly ?

or

As you might be almost there why dont you try the following  till you complete the client import [ Infinite logging ]

1493587 - DB6: Use of infinite logging or secondary log files [ Take a full backup before you start ]

Once you complete the client copy  enable archive logging once again and take a full offline backup

Thanks ,

Manu

[Former Member]

Hi Omkar,

Other option instead of increasing File System, or log files is...

You can try excluding a few bigger tables duing client import, check the import log as an where the secondary log gets full.

Please use reoport RSCCEXPT –  to add table name in the tab Table in the target client.

For more info check SAP note: 70290

You can later on import the table.

Regards,

Prithviraj.

[Former Member]

Hi Manu,

Thanks for quick reply. I attaching the output result of command.And size of log_dir is

/dev/vglog_dir/lvol21   52396032   34151576  17104219   67% /db2/QAS/log_dir (67% is allocated).

Regards,

Omkar M.

LOG1.txt.zip

[manumohandas82]

Hi Omkar ,

Please let me know in GB how much space is available in total for /db2/QAS/Log _dir  [ I dont know hwther the values you supplied is in KB or Bytes ]

[ I am assuming you have 50 GB of total space configured for this FS ]

From your parameyers the  total space you have allocated for primary log files is (  LOGFILSIZ *4KB * LOGPRIMARY) = 5734400 KB or 5.4  GB

Total space = 5.4  GB +  ( LOGSECOND * LOGFILSIZ *4KB ) = 27 + 5.4 GB

The recommendation from SAP is to keep LOG second = 0

So make the following changes

Case A : You are ok to restart your database

1 ) db2 update db  CFG FOR qas using  LOGPRIMARY  170

[ Assuming  you have 51 GB of total space available of which 48 GB will now be occupied  ]

2 ) db2 update db   CFG FOR qas  using LOGSECOND 0

restart your database and try once again

Case B  : You are  Not ok to restart your database

db2 update db   CFG FOR qas  using LOGSECOND 150

Also

I increase the size to 70000 using command db2 UPDATE cfg for QAS using LOGFILSIZ 70000

Did you restart after the above change ?

Thanks ,

Manu

[Former Member]

Hi Omkar,

I think the problem is somewhere else.

Can you use the  command  ./db2 get dbm cfg | grep DIAGPATH  to find the db2diag.log file.

In the db2diag.log file please copy and paste the last 500 lines  to analyse the error.

Regards,

Suresh Daniel.

[Former Member]

Hi Suresh,

Here I am attaching the output of db2 get db cfg for QAS so please check whether Primary log size and secondary log size is ok or not. and also I want to know that size is in kb or mb. please help me ASAP.

Regards,

Omkar M

log_cfg.txt.zip

[manumohandas82]

Hi Omkar ,

Transaction log filles situations are caused if your log_dir directory reaches 100% [ This is not archive log getting filled up ]

Please let us know what is the total size allocated to the log_dir volume . Also let us know the following parameters

db2 get db cfg for <sid> | grep -i LOG

Thanks ,

Manu

[Former Member]

Hi Omkar,

As already informed you had primary + secondary log full situation in your system.

You have 20 primary and 100 secondary log files allocated each of 280 MB in size that means total ~34 GB (280*120) of logs would be written in your log directory /db2/QAS/log_dir/NODE0000/

So make sure that the File system has enough space for this directory aleast double.

LOGFILSIZ = value * 4 KB. (the value you assign to this parameter which is than multiplied by 4 and the result is in KB)

Regards,

Prithviraj.

[Former Member]

Hi Omkar,

Use the command bdf after logging into the system as root user.

The bdf will list all the file system partitions. The archive disk (db2)

partition will be full with 100%. If it is full then delete the logs after

copying the log files to some other location.

Hope this helps you.

Regards,

Suresh Daniel

[Former Member]

Hi Suresh,

Thanks for quick reply. below is the bdf screenshot in that I could not see any log_archive.  so where is the location of archive. and also there is no drive which is 100% full. when I started importing the request at that time size of the log_dir got increasing and increased to 67% max.

Regards,

Omkar M.

[Former Member]

Hi Omkar,

You can find out using db2 get db cfg for QAS as db2qas user.

Your /dump partion is 100% full  I think your log_archive is lying

there. this is my guess. You pls check that first.

Regards,

Suresh Daniel.