IBM Support

How to restore SQLSGF files

Question & Answer


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… 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.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database Objects\/Config - Tablespaces","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.1;10.5;9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21682091