There are three common errors or warnings that an application using the DB2 Call Level Interface (CLI) driver might receive:
1. CLI0109E - "String Data Right Truncation".
2. SQL0302N - "The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use."
3. CLI0002W - "Data Truncated".
What is the difference, and how do you solve these types of problems?
All three error messages describe a truncation of some kind. The difference is where the truncation of the data is taking place.
Here are the differences and what to check for in your application to ensure the error is resolved:
1. CLI0109E - A "String Data Right Truncation" will happen when the application is binding data to a parameter marker for a statement of some kind. The application has to specify a maximum size for the parameter marker when using the SQLBindParameter() API or the SQLSetParam() API. If the "data" that is being bound to this parameter is larger then the maximum size specified (that is, the value defined for cbColDef) for that parameter, then the CLI will return this error so that it prevents data corruption in the database. The solution is to either ensure the data is small enough to fit in the cbColDef, or increase the size of the buffer.
2. SQL0302N - This error is very similar to the CLI0109E error, however the problem here is that the application does specify a large enough cbColDef for the data but the column size (or parameter if working with stored procedures) at the database server is too small for the data. In this case it is the database server itself returning the error to the application as opposed to the CLI driver returning the error. The solution here would be to either ensure the data is small enough, or the actual column size at the database server is altered to be large enough for the data.
3. CLI0002W - This is just a warning, but can be critical for an application. The DB2 CLI driver will return a CLI0002W warning when the application buffer specified for the data to be returned on an SQLGetData() call is too small for the data. Here the truncation is happening within the application itself. CLI is simply informing the application layer that the data was truncated because the buffer that was used in the application is too small for the data. The reason why this is a warning is because the database server itself is not affected by this truncation, meaning there will not be a corruption in the database because of this warning. The solution is to just increase the size of the application buffer used by the SQLGetData() API.
Note: Many applications do deal with mixed code page environments, where the application code page is different from the codepage of the database server. In these environments it is very possible that data can be expaned when being sent to the server by the application or vice-versa. For these cases it is always useful to ensure the application buffers and columns for the database are large enough to account for any codepage expansion.