Tailoring the SQLCODE mapping

By default, DB2 Connect™ maps SQLCODEs and tokens from each host or System i™ database server to the appropriate DB2^® SQLCODEs. The following files are copies of the default SQLCODE mapping:

dcs1dsn.map maps DB2 Universal Database (UDB) for OS/390^® and z/OS^® and DB2 for z/OS SQLCODEs.
dcs1ari.map maps DB2 UDB for VSE and VM SQLCODEs.
dcs1qsq.map maps DB2 UDB for iSeries and DB2 for i5/OS SQLCODEs.

No mapping is required for DB2 on Linux^® or UNIX^® operating systems.

If you want to override the default SQLCODE mapping or you are using a host or System i database server that does not have SQLCODE mapping (a non-IBM database server), you can copy one of these files and use it as the basis for your new SQLCODE mapping file. By copying the file rather than editing it directly, you ensure that you can always refer to the original SQLCODE mapping, if necessary.
Specify the file name of your new SQLCODE mapping file in the parameter string of the DCS Directory.
Each mapping file is an ASCII file, which is created and edited using an ASCII editor. At initial installation, the file is stored in the map directory in the installation path.
The file can contain the following special types of lines:

&&

The logical beginning of the file. All lines before the first occurrence of && are considered free-form comments and ignored. If the file contains nothing after &&, no SQLCODE mapping is performed. You can also turn off SQLCODE mapping with the NOMAP parameter, as described previously.

*

As the first character on a line, indicates a comment.

W

As the only character on a line, indicates that warning flags should be remapped. By default, the original warning flags are passed. The W must be uppercase.

All other lines after && must be either blank or mapping statements in the following form:
```
   input_code [, output_code [, token_list]]
```
The input_code represents one of the following:
sqlcode

The SQLCODE from the host or System i database server.

U

All undefined negative SQLCODEs (those not listed in this file) are mapped to the specified output_code. If no output_code is specified on this line, the original SQLCODE is used. This character must be uppercase.

P

All undefined positive SQLCODEs (those not listed in this file) are mapped to the specified output_code. If no output_code is specified on this line, the original SQLCODE is used. This character must be uppercase.

ccnn

The SQLSTATE class code from the host or System i database server. nn is one of the following:
00

Unqualified successful completion

01

Warning

02

No data

21

Cardinality violation

22

Data exception

23

Constraint violation

24

Invalid cursor state

26

Invalid SQL statement identifier

40

Transaction Rollback

42

Access violation

51

Invalid application state

55

Object not in prerequisite state

56

Miscellaneous SQL or Product Error

57

Resource not available or operator intervention

58

System error

The specified output_code is used for all SQLCODEs with this class code that are not specified explicitly in the mapping file. If no output_code is specified on this line, the original SQLCODE is mapped to itself with no tokens copied over.

The characters cc must be lowercase.

If the same input_code appears more than once in the mapping file, the first occurrence is used. The output_code represents the output SQLCODE. If no value is specified, the original SQLCODE is used.
If you specify an output code, you can also specify one of the following:
(s)

The input SQLCODE plus the product ID (ARI, DSN or QSQ) will be put into the SQLCA message token field.
The original SQLCODE is returned as the only token. This option is designed to handle undefined SQLCODEs, with the exception of +965 and -969. If +965 or -969 is the output_code, the token list returned in the SQLERRMC field of the SQLCA includes the original SQLCODE, followed by the product identifier, followed by the original token list.

The character s must be lowercase.

(token-list)

A list of tokens, separated by commas. Specify only a comma to skip a particular token. For example, the form (,t2,,t4) means that the first and third output tokens are null. Each token has the form of a number (n), optionally preceded by c, optionally followed by c or i. It is interpreted as follows:
c

The data type of the token in this position is CHAR (the default). If c comes before n, it refers to the input token; if it comes after n, it refers to the output token. The character c must be lowercase.

i

The data type of the token in this position is INTEGER. If i comes after n, it refers to the output token. i should not come before n, because IBM^® host or System i database server products support only CHAR tokens. The character i must be lowercase.

n

A number or numbers indicating which host or System i database server tokens are used. They are arranged in the order desired for placement in the output SQLCA. The number indicates the host or System i database server token; the arrangement indicates the order in which the tokens will be placed in the SQLCA.
For example, the host or System i database server might return two tokens, 1 and 2. If you want token 2 to appear before token 1 in the output SQLCA, specify (2,1).

Multiple token numbers can be combined to form one CHAR output token by connecting them with periods.

Commas are used to separate output tokens. If no token is specified before a comma, no output token is included in the SQLCA for that position. Any tokens occurring in the output SQLCA following the last specified token are mapped to a null token.

Figure 1 shows a sample SQLCODE mapping file. Figure 1. An SQLCODE Mapping File

&&
  -007    ,   -007   ,   (1)
  -010   -060    ,   -171   ,   (2)
…
  -204    ,   -204   ,   (c1.2c)
…
  -633    ,   -206   ,   (,c1i)
 
  -30021  ,   -30021 ,   (c1c,c2c)
 
  cc00    ,   +000 …
  U       ,   -969   ,   (s)
  P       ,   +965   ,   (s)

The following descriptions correspond to the matching line number in the previous figure:

The SQLCODE is mapped from -007 to -007. The first input token received from the host or System i database server is used as the first output token, and it defaults to CHAR. No other tokens are transferred.
The SQLCODE is mapped from -010 to -010 (no output SQLCODE is specified). No tokens are put into the output SQLCA.
The SQLCODE is mapped from -060 to -171. The first input token received from the host or System i database server is discarded. The second is used as the first token in the output SQLCA, and it is CHAR. There is no second token in the output SQLCA.
The SQLCODE is mapped from -204 to -204. The first and second tokens received from the host or System i database server are CHAR. These two input tokens are combined to form one CHAR output token, which will be the first output token in the SQLCA.
The SQLCODE is mapped from -633 to -206. The first input token received from the host or System i database server is CHAR. It is converted to INTEGER and is used as the second token in the output SQLCA. The first token in the output SQLCA is null, as indicated by a comma.
The SQLCODE is mapped from -30021 to -30021. The first and second input tokens received from the host or System i database server are CHAR, and they are used as the first and second tokens in the output SQLCA.
All SQLCODEs in SQLCAs with SQLSTATEs in the 00 class will be mapped to SQLCODE +000.
All undefined SQLCODEs are mapped to -969. This option should be used only if all mappable codes are listed, including all those that are identical and require no mapping. The (s) option indicates that the token list to be returned in the SQLERRMC field of the SQLCA includes the original SQLCODE, followed by the product the error occurred in, followed by the original token list. If the U entry is not included, all unlisted codes are passed without any mapping.
All undefined positive SQLCODEs are mapped to +965. This option should be used only if all mappable codes are listed, including all those that are identical and require no mapping. The (s) option indicates that the token list to be returned in the SQLERRMC field of the SQLCA includes the original SQLCODE, followed by the product the warning occurred in, followed by the original token list. If the P entry is not included, all unlisted positive codes are passed without any mapping.

Parent topic: SQLCODE mapping