Troubleshooting
Problem
SQL1051N error can be thrown when using partitions expressions in commands to create storage or tablespaces container paths on multi-partition (DPF) environments. SQL2036N or SQL1174N errors can be thrown when using partition expressions in DB2 commands such as "BACKUP DATABASE" in DPF environments.
Symptom
Example 1: SQL1051N
When using partition expressions to create storage or automatic tablespace container paths, for example, suppose separate storage paths in DPF environment with four physical partitions is to be created as follows:
/home/db2inst1/db2db/db2inst1/TestDB/node0000/database name/T#######/C#######.EXT
/home/db2inst1/db2db/db2inst1/TestDB/node0001/database name/T#######/C#######.EXT
/home/db2inst1/db2db/db2inst1/TestDB/node0002/database name/T#######/C#######.EXT
/home/db2inst1/db2db/db2inst1/TestDB/node0003/database name/T#######/C#######.EXT
The following command is executed:
$ db2 "CREATE STOGROUP BOB ON '/home/db2inst1/db2db/db2inst1/TestDB/node000 $N'"
SQL1051N The path "/home/db2inst1/db2db/db2inst1/TestDB/node000" does not exist or is not valid. SQLSTATE=57019
Example 2: SQL2036N
When using partition expressions with general DB2 commands, for example, to take a backup in DPF environment to four separate nodes:
/db2fs/db2inst1/node0000/backups
/db2fs/db2inst1/node0001/backups
/db2fs/db2inst1/node0002/backups
/db2fs/db2inst1/node0003/backups
The following command is executed:
db2 "backup db TestDB on all dbpartitionnums to '/db2fs/db2inst1/node $4N/backups' without prompting"
SQL2036N The path for the file, named pipe, or device "/db2fs/db2inst1/node N/backups" is not valid.
Example 3: SQL1174N
A slight modification to the command in Example 2 by adding an escape character before partition expression. It still fails:
db2 "backup database testdb to '/db2fs/db2inst1/node \$4N/backups' without prompting"
SQL1174N Invalid or incorrect use of database partition expression in path "". Reason code = "". SQLSTATE=5U012
Cause
The DB2 parser can be picking up the whole expression without substituting a value for the partition expression. Additionally, the shell can be evaluating $N as "the value of an environment variable called 'N'" which would cause the command to fail.
Resolving The Problem
1. Make sure that the paths actually exist on the filesystem. For example,
In Example 1, the following path needs to exist (for automatic storage):
/home/db2inst1/db2db/db2inst1/TestDB/node0000
/home/db2inst1/db2db/db2inst1/TestDB/node0001
/home/db2inst1/db2db/db2inst1/TestDB/node0002
/home/db2inst1/db2db/db2inst1/TestDB/node0003
In Example 2, the following path needs to exist:
/db2fs/db2inst1/node0000/backups
/db2fs/db2inst1/node0001/backups
/db2fs/db2inst1/node0002/backups
/db2fs/db2inst1/node0003/backups
2. Use an escape character '\' before the partition expression ($N)
3. Make sure that there is a space after the partition expression if there are sub-paths under the partition expression
Solution to Example 1: Add escape character '\' before partition expression
$ db2 "CREATE STOGROUP JOHN ON '/home/db2inst1/db2db/db2inst1/TestDB/node000 \$N'
DB20000I The SQL command completed successfully.
Solution to Examples 2 and 3: Make sure there is an escape character '\' before partition expression and space after partition expression
db2 "backup database testdb to '/db2fs/db2inst1/node \$4N /backups' without prompting"
Backup successful. The timestamp for this backup image is : 20140224151941
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21665588