IBM Support

How to restore SQLSGF files

Technote (FAQ)


Question

Sometimes the SQLSGF.1 file and SQLSGF.2 file could be missing due to some unknown reason, e.g. hardware/disk issues.

The 2 SQLSGF files contain storage path information associated with the automatic storage feature of a database. More details about the 2 files can be found in following link:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0005420.html

Without the 2 SQLSGF files, ONLINE BACKUP would fail with following error:
===========================================
2014-07-30-06.15.19.308782-240 I3518A803 LEVEL: Severe
PID : 7930292 TID : 22301 PROC : db2sysc
INSTANCE: qiaogz NODE : 000 DB : TEST1
APPHDL : 0-94 APPID: *LOCAL.qiaogz.140730101519
AUTHID : QIAOGZ
EDUID : 22301 EDUNAME: db2bm.21787.0 (TEST1)
FUNCTION: DB2 UDB, buffer pool services, sqlbOpenMirroredFiles, probe:20
MESSAGE : ZRC=0x860F000A=-2045837302=SQLO_FNEX "File not found."
DIA8411C A file "" could not be found.
DATA #1 : String, 28 bytes
Error opening mirrored files
DATA #2 : String, 56 bytes
/home/hotelaix8/qiaogz/qiaogz/NODE0000/SQL00002/SQLSGF.1
DATA #3 : Pointer, 8 bytes
0x0780000000b08e39
DATA #4 : Pointer, 8 bytes
0x070000006a7fdc18
DATA #5 : Pointer, 8 bytes
0x070000006a7fdc20

2014-07-30-06.15.19.318035-240 E4322A518 LEVEL: Severe
PID : 7930292 TID : 22301 PROC : db2sysc
INSTANCE: qiaogz NODE : 000 DB : TEST1
APPHDL : 0-94 APPID: *LOCAL.qiaogz.140730101519
AUTHID : QIAOGZ
EDUID : 22301 EDUNAME: db2bm.21787.0 (TEST1)
FUNCTION: DB2 UDB, database utilities, sqlubProcessSGF, probe:916
MESSAGE : ZRC=0x860F000A=-2045837302=SQLO_FNEX "File not found."
DIA8411C A file "" could not be found.

2014-07-30-06.15.19.318654-240 E4841A553 LEVEL: Severe
PID : 7930292 TID : 22301 PROC : db2sysc
INSTANCE: qiaogz NODE : 000 DB : TEST1
APPHDL : 0-94 APPID: *LOCAL.qiaogz.140730101519
AUTHID : QIAOGZ
EDUID : 22301 EDUNAME: db2bm.21787.0 (TEST1)
FUNCTION: DB2 UDB, database utilities, sqlubProcessSGF, probe:918
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
-980
DATA #2 : Hexdump, 8 bytes
0x0000000112B90998 : 860F 000A 0000 000A ........
===========================================

Furthermore, if the database was shut down, it would faild to start up without the 2 SQLSGF files (SQL0902C returned):
===========================================
2014-07-30-06.17.06.750790-240 I2501A793 LEVEL: Severe
PID : 7930292 TID : 1544 PROC : db2sysc
INSTANCE: qiaogz NODE : 000 DB : TEST1
APPHDL : 0-96 APPID: *LOCAL.qiaogz.140730101706
AUTHID : QIAOGZ
EDUID : 1544 EDUNAME: db2agent (TEST1)
FUNCTION: DB2 UDB, buffer pool services, sqlbInitStorageGroupTable, probe:25
MESSAGE : ZRC=0x87020002=-2029912062=SQLB_BPSE "Debug logic error detected"
DIA8501C A buffer pool logic error has occurred.
DATA #1 : String, 56 bytes
/home/hotelaix8/qiaogz/qiaogz/NODE0000/SQL00002/SQLSGF.2
DATA #2 : Boolean, 1 bytes
false
DATA #3 : Boolean, 1 bytes
true
DATA #4 : Boolean, 1 bytes
false
DATA #5 : Boolean, 1 bytes
false
DATA #6 : Boolean, 1 bytes
false

2014-07-30-06.17.06.752329-240 I3295A755 LEVEL: Severe
PID : 7930292 TID : 1544 PROC : db2sysc
INSTANCE: qiaogz NODE : 000 DB : TEST1
APPHDL : 0-96 APPID: *LOCAL.qiaogz.140730101706
AUTHID : QIAOGZ
EDUID : 1544 EDUNAME: db2agent (TEST1)
FUNCTION: DB2 UDB, buffer pool services, sqlbInitStorageGroupTable, probe:25
MESSAGE : ZRC=0x87020002=-2029912062=SQLB_BPSE "Debug logic error detected"
DIA8501C A buffer pool logic error has occurred.
DATA #1 : String, 44 bytes
Failed to initialize the storage group table
DATA #2 : Pointer, 8 bytes
0x07800000010c85d9
DATA #3 : Pointer, 8 bytes
0x07800000010ccf28
DATA #4 : Pointer, 8 bytes
0x0780000000b10080

2014-07-30-06.17.06.754689-240 I4051A527 LEVEL: Error
PID : 7930292 TID : 1544 PROC : db2sysc
INSTANCE: qiaogz NODE : 000 DB : TEST1
APPHDL : 0-96 APPID: *LOCAL.qiaogz.140730101706
AUTHID : QIAOGZ
EDUID : 1544 EDUNAME: db2agent (TEST1)
FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:15
MESSAGE : ZRC=0x87020002=-2029912062=SQLB_BPSE "Debug logic error detected"
DIA8501C A buffer pool logic error has occurred.
===========================================

The question is: how to restore the 2 SQLSGF files to avoid the problems mentioned above?

Cause

DB2 doesn't delete the 2 SQLSGF files, so the files should be removed due to non-DB2 issues.

Answer

The SQLSGF files can be restored by any of the following ways:

1. Restore the entire database from a good backup, and roll forward the database to most recent point in time if needed.

2. Restore the missing SQLSGF files from OS backup image.

3. Rebuild the 2 SQLSGF files by following procedure:

1) In the original database, issue following command to obtain information about the Automatic Storage configurations:
db2pd -db < db name > -storagepaths
The information can also be retrieved by calling table function "SNAP_GET_STORAGE_PATHS_V97":
db2 "select * from TABLE(SNAP_GET_STORAGE_PATHS_V97(NULL,-1))"

2) Then create a new database on another machine, with the same Automatic Storage path configurations obtained in previous step:
db2 "CREATE DATABASE < db name > AUTOMATIC STORAGE YES ON <PATH 1>, <(><PATH 2>, ... <PATH N>
DBPATH ON <DB DIRECTORY> USING CODESET UTF-8 TERRITORY en_US COLLATE USING IDENTITY_16BIT PAGESIZE <PageSize>"
Please Note:
-- The Automatic Storage paths MUST be the very same as the paths used in the original database.
-- All paths for the automatic storage has to be created before running the command.
-- The paths must be owned by the instance user.

3) Find the 2 SQLSGF files in database directory of the new database, and copy them to database directory of the original database.

Document information

More support for: DB2 for Linux, UNIX and Windows
Database Objects/Config - Tablespaces

Software version: 9.7, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1682091

Modified date: 22 March 2016


Translate this page: