SQL1051N, SQL2036N, or SQL1174N errors when using partition expressions in commands on DPF

Technote (troubleshooting)


Problem(Abstract)

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

Using Database Partition Expressions
Container names in automatic storage table spaces

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7, 10.1, 10.5

Operating system(s):

AIX, HP-UX, Linux, Solaris

Reference #:

1665588

Modified date:

2014-02-24

Translate my page

Machine Translation

Content navigation