DB2 archive log backups would not be executed if the selection list with the same name already exists

Article:TECH125000  |  Created: 2010-01-03  |  Updated: 2010-01-07  |  Article URL http://www.symantec.com/docs/TECH125000
Article Type
Technical Solution

Product(s)

Environment

Issue



DB2 archive log backups would not be executed if the selection list with the same name already exists

Error



-536837669:A selection list with the same name already exists.
0x80040e2f Cannot insert duplicate key row in object 'dbo.Scripts' with unique index 'uidx_Scripts_ScriptName'.
ADM1848W Failed to archive log file ...

Solution



DB2 archive log backups would not be executed if the selection list with the same name already exists.
If this issue occurs, the backup job instance would not be created, instead the following errors are reported on the SGMon log and application event log.

Note: This issue would occur with any type of DBA-initiated jobs. For example, when performing an Oracle restore job initiated from RMAN, the job instance would not be created if the selection list with the same name already existed.

===== SGMon.log =====
BESERVER: [03/02/10 14:59:41] [1716]     -1 SQLLog(6531):Database Exception Context:m_QueryTimeoutInSeconds=30 sql={? = CALL DBO.SAVESCRIPT(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Error:-536837662: :: -2147217873:The statement has been terminated. :: DB Error Set 0: native=0xe25 source=Microsoft OLE DB Provider for SQL Server hr=0x80040e2f The statement has been terminated. :: DB Error Set 1: native=0xa29 source=Microsoft OLE DB Provider for SQL Server hr=0x80040e2f Cannot insert duplicate key row in object 'dbo.Scripts' with unique index 'uidx_Scripts_ScriptName'.
BESERVER: [03/02/10 14:59:41] [1716]     -1 Error(9/8): -536837669:A selection list with the same name already exists.
BENGINE:  [03/02/10 14:59:41] [2544]     BEMSDKHelper::CreateBackupScript: CBemScript::Insert() failed.
BENGINE:  [03/02/10 14:59:41] [2544]     BEMSDKHelper::ProcessErrors: ErrorCode=-536837669, Msg=A selection list with the same name already exists.
===== end of SGMon.log =====

===== event log when using userexit program =====
3/2/2010 3:04:20 PM DB2-0 Warning None 5 N/A SERVER1
2010-03-02-15.04.20.920001   Instance:DB2   Node:000
PID:1676(db2syscs.exe)   TID:2272   Appid:none
data protection services  sqlpgArchiveLogFile Probe:3150

ADM1848W  Failed to archive log file "S0000008.LOG" to "USEREXIT" from
"C:\DB2\NODE0000\SQL00001\SQLOGDIR\".
        "
3/2/2010 3:04:20 PM DB2-0 Error None 4 N/A SERVER1
2010-03-02-15.04.20.920000   Instance:DB2   Node:000
PID:1676(db2syscs.exe)   TID:2272   Appid:none
data protection services  sqlpgInvokeUserexit Probe:1550

ADM1833E  The user exit program returned an error when archiving log file
"S0000008.LOG" from "C:\DB2\NODE0000\SQL00001\SQLOGDIR\" for database "SAMPLE".
The error code was "8".

3/2/2010 3:04:18 PM Backup Exec Information (65535) 57345 N/A SERVER1
Database Exception Context:m_QueryTimeoutInSeconds=30 sql={? = CALL DBO.SAVESCRIPT(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Error:-536837662: :: -2147217873:The statement has been terminated. :: DB Error Set 0: native=0xe25 source=Microsoft OLE DB Provider for SQL Server hr=0x80040e2f The statement has been terminated. :: DB Error Set 1: native=0xa29 source=Microsoft OLE DB Provider for SQL Server hr=0x80040e2f Cannot insert duplicate key row in object 'dbo.Scripts' with unique index 'uidx_Scripts_ScriptName'.
===== end of event log when using userexit program =====

===== event log when using vender dll (db2sqluv.dll) =====
3/2/2010 3:30:01 PM DB2-0 Warning None 5 N/A SERVER1
2010-03-02-15.30.01.717000   Instance:DB2   Node:000
PID:2724(db2syscs.exe)   TID:2632   Appid:none
data protection services  sqlpgArchiveLogFile Probe:3150

ADM1848W  Failed to archive log file "S0000014.LOG" to "VENDOR chain 0" from
"C:\DB2\NODE0000\SQL00001\SQLOGDIR\".
        "
3/2/2010 3:30:01 PM Backup Exec Error None 58496 N/A SERVER1
Failed to load db2sqluv library due to the following reason
BEError(0xe0000f0b) An error occurred attempting to connect to the Backup Exec Server service.
The operation was performed on the DB2 database(SAMPLE) in instance(DB2).

3/2/2010 3:30:01 PM Backup Exec Information (65535) 57345 N/A SERVER1
Database Exception Context:m_QueryTimeoutInSeconds=30 sql={? = CALL DBO.SAVESCRIPT(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Error:-536837662: :: -2147217873:The statement has been terminated. :: DB Error Set 0: native=0xe25 source=Microsoft OLE DB Provider for SQL Server hr=0x80040e2f The statement has been terminated. :: DB Error Set 1: native=0xa29 source=Microsoft OLE DB Provider for SQL Server hr=0x80040e2f Cannot insert duplicate key row in object 'dbo.Scripts' with unique index 'uidx_Scripts_ScriptName'.
===== end of event log when using vender dll (db2sqluv.dll) =====


Solution:

Please delete existing Backup Selection Lists regularly. (Figure 1)

Figure 1.
 


If this issue occurs during restoration, please follow the next steps.

1. Take backup of Backup Exec database (BEDB) with BEUtility

Please refer to TechNote 337960 for more information to take a backup of BEDB
 http://support.veritas.com/docs/337960

2. Rename the BEDB.bak to an unique name, E.G. bedb.20100303.bak.

3. Copy the following script to a file, E.G. del-restore-sel.sql.

===== begin of sql script =====
BEGIN TRANSACTION

DELETE Scripts WHERE ScriptID IN (
SELECT s.ScriptID
FROM Scripts AS s
LEFT OUTER JOIN Jobs AS j ON s.ScriptID = j.ScriptID AND s.PartitionID = j.PartitionID
WHERE (j.Scriptid IS NULL) AND (s.ScriptType = 600) AND (s.ScriptSubType = 0)
)

DELETE RestoreScriptEntries WHERE ScriptId IN (
SELECT e.ScriptID
FROM RestoreScriptEntries AS e
LEFT OUTER JOIN Scripts AS s ON e.ScriptID = s.ScriptID
WHERE (s.ScriptID IS NULL)
)

COMMIT TRANSACTION
===== end of sql script =====

4. Open command prompt.

5. Perform the following command with the created file by step #3.

osql -E -S .\BKUPEXEC -d BEDB -e -i del-restore-sel.sql


Supplemental Materials

SourceETrack
Value864670
DescriptionVXBSA: Backup selection list corresponding to dba initiated jobs should also be deleted

SourceError Code
Value-536837669
DescriptionA selection list with the same name already exists

SourceError Code
Value0x80040e2f
DescriptionCannot insert duplicate key row in object 'dbo.Scripts' with unique index 'uidx_Scripts_ScriptName'

SourceError Code
ValueADM1848W
DescriptionFailed to archive log file "S0000000.LOG" to "USEREXIT" from "C:\DB2\NODE0000\SQL00001\SQLOGDIR\"

SourceError Code
ValueADM1848W
DescriptionFailed to archive log file "S0000000.LOG" to "VENDOR chain 0" from "C:\DB2\NODE0000\SQL00001\SQLOGDIR\"

SourceError Code
ValueADM1833E
DescriptionThe user exit program returned an error when archiving log file... The error code was "8"

SourceError Code
Value0xe0000f0b
DescriptionAn error occurred attempting to connect to the Backup Exec Server service


Legacy ID



346706


Article URL http://www.symantec.com/docs/TECH125000


Terms of use for this information are found in Legal Notices