How to prevent SQLCODE -803 in IBM DB2?

Hi ! 👋

On this April Fool's day 😀, we will be seeing how to prevent a specific SQL error code (-803) which is issued, when something isn't right when a user insert/update values in a Db2 table.

What's SQLCODE -803 🤔?

When a user try to insert/update a value in column which is constrained to have unique values and if the inserted/updated value is already present in the table, SQLCODE -803 will be issued. Consequently, the insert/update statement will not be processed. 

More info about the error code can be found 👉 here.

As a brief aside...

To better understand the stuff discussed in the remainder of this post, we need access to IBM DB2 database. At the time of writing this post, I just had Mainframe access to IBM's Master the Mainframe 2020 learning system and it didn't allow participants to try Db2 as the focus was put on Zowe, VS Code and plenty of other new stuff.

I started searching 🔍 on Google with phrases like 'how to practice db2 at home', 'practice db2 online' and that's how I found the Db2 Community edition Docker image. Docker wasn't new to me as I had already had prior experience by working on the last two challenges in the Part 3 of IBM's Master the Mainframe 2020 contest. 

Docker 🐬 is a technology which allows solutions that run in a standardized environment to be picked and made to run anywhere else. This means that every little file, library, piece of code, even full software pacakges and operating systems can be packaged up into a Docker Container 📦 and made available for distribution.

- Quoted text from Challenge instructions of ANSB1 - Master the Mainframe 2020.

 

IBM has made available, a Docker image (read image as snapshot) of Db2 11.5.4 Community Edition, so all that we have to do is to install this image on your local machine 💻. The procedure to install can be found 👉 here. 

Note: The procedure assumes that you've already installed Docker Desktop on your system and you most probably do if you've finished the last 2 challenges in Part 3 of MTM2020. Else, you can follow the guidelines given in the procedure to install Docker Desktop first. Docker Desktop is nothing but a software which is used to run the Docker images as applications. 

Alternatively, IBM allows us to download and install IBM Db2 Community edition, a free to download, use and redistribute edition of the IBM Db2 data server, which has both XML database and relational database management system features. But, I recommend the Docker image method as I found it to be easy and simple. You can install the docker image and start working with sample tables just over a coffee 🍵 break. 

Alright! Let's get down to business. 

Kickstarting the Db2 Docker Image and creating a sample database...

After completing the procedure to install the IBM Db2 Community Edition Docker image on your machine, you should be able to see the container up and running in the Docker Desktop 👏. 

This image shows the container named db2server in running state. 

Where do we issue SQL commands in Db2 Container 🤔?

Open the command prompt and type the following command to access the running Db2 instance within your Docker container:

docker exec -ti db2server bash -c "su – db2inst1"

If the command is successful, you'll see the last login details along with Db2 instance db2inst1 connected to the container named db2server with a $ symbol at the end.

Getting into the container is successful. We can start executing DB2 queries. 

We're all set to issue Db2 commands. Let's create the Db2 SAMPLE database, which will have a set of tables pre-loaded with data. We can then use these tables and their data in our queries. The procedure to create the sample database can be found 👉 here.

Commands used to create DB2 SAMPLE database.

From the list of tables displayed after issuing db2 list tables command, let's use the EMPLOYEE table to reproduce SQLCODE -803. 

 

Running SQL statements in IBM Db2 Docker container is more or less similar to running statements in SPUFI in z/OS.  

The EMPLOYEE table has got 42 records. 

All the rows of EMPLOYEE table are displayed after the execution of SELECT SQL query.

In order to reproduce -803 SQLCODE, let's try to re-insert the last record to the EMPLOYEE table as I knew that the first column of the table i.e., EMPNO can only have unique values. 

Make note of the rectangle boxes. The first box shows the last row from the previously executed SELECT query. The second box shows the INSERT SQL query with values same as that of the last row. The third box shows the SQL error code, SQL0803N and the error description.

The error code after executing the INSERT SQL query is SQL0803N, where 0803N is in Zoned decimal format. The last character, N in '0803N', stores the negative sign as well as the value '3'.

Preventing SQLCODE -803...

Method 1: We can write an  SELECT SQL query in COBOL Db2 program before INSERTing a row. If the row is already present, SQLCODE will be 0. Else, SQLCODE will be +100 (no row(s) found). Evaluate the SQLCODE after the execution of the SELECT query and INSERT accordingly. 

Method 2: We can make use of the MERGE INTO statement. The INSERT and UPDATE statements can be incorporated into a MERGE statement by taking an input data source, comparing it to the contents of the existing table and performing one of the actions (INSERT or UPDATE) if the record exists or it does not. 

For example, let's try to update an existing row in the EMPLOYEE table and insert a new row to the table - all these in one statement.

The full statement is as follows. I've used different colors to describe the purpose of each part of the statement.  

MERGE INTO EMPLOYEE AS EM 

USING (VALUES (200340,'ROY','R','ALONZO','E21',5698,'07/05/1997','FIELDREP',16,'M','05/17/1956',31840,500,1907),(200341,'SRINIVASAN','','JV','D11',2882,'03/05/2021','DESIGNER',16,'M','02/03/1992',31840,500,1907)) 

AS ET (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) 

ON (EM.EMPNO = ET.EMPNO) 

WHEN MATCHED THEN UPDATE 

SET EM.BONUS = 1000 

WHEN NOT MATCHED THEN INSERT (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM) VALUES (ET.EMPNO,ET.FIRSTNME,ET.MIDINIT,ET.LASTNAME,ET.WORKDEPT,ET.PHONENO,ET.HIREDATE,ET.JOB,ET.EDLEVEL,ET.SEX,ET.BIRTHDATE,ET.SALARY,ET.BONUS,ET.COMM)

The first statement, highlighted in blue color, is the MERGE INTO statement, which is used to identify the name of the table (in our case, it's EMPLOYEE) to which data will be inserted or updated. The AS EM code is used to specify the alias for the EMPLOYEE table that can be referenced later in the code.  

The second part of the statement, highlighted in green color, specifies the input data source i.e., two sets of values. The first set (with EMPNO as 200340) is already existing in the table and the second set (with EMPNO as 200341) is a new row that has to be inserted into the table. When you're coding the MERGE INTO statement in a COBOL Db2 program, then the input source can be specified as an host variable array. 

The third part of the statement, highlighted in purple color, is AS ET which is used to specify the alias for the input source. 

The fourth part of the statement, highlighted in orange color, are the column names specified to associate the input data with the target data during any insert or update operations that follow. 

The fifth part of the statement, highlighted in black color, is the search criteria upon which any action will be taken. In this case, if the EMPNO value in the EMPLOYEE table is equal to the input source's EMPNO value, then the actions mentioned in the next part of the statement will be invoked. 

The sixth and last part of the statement, highlighted in brown color, indicates that when the search criteria is matched, an update will occur in the EMPLOYEE table, updating the BONUS column to 1000. The WHEN NOT MATCHED code indicates that if a record doesn't exist, then an insert action will occur and add all the necessary column values from the input source into the EMPLOYEE table. 

Let's run the full statement in Db2 container. 

The SQL command completed successfully.

Let's check the EMPLOYEE table now. 

Note the last 2 rows. The last but one row's BONUS column is updated to 1000. The last row is newly inserted. There are now 43 rows in EMPLOYEE table as opposed to the 42 rows, before executing the MERGE INTO statement. 

We've hit the bottom of this post...

In this post, we discussed the following: 

• How to install IBM Db2 Docker image on your machine?
• How to create Db2 SAMPLE database containing a set of tables with pre loaded data after installing and configuring Db2 Community Edition Docker image on your machine?
• How to issue SQL statements on Db2 Community Edition Docker image?
• How to prevent SQLCODE -803 using MERGE INTO statement?

Hope this helps! Should you have any questions/feedback please post it in the Comments section below. Thx!