Error: DB2 SQL Error: SQLCODE=-117, SQLSTATE=42802, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE NUMBER OF VALUES ASSIGNED
IS NOT THE SAME AS THE NUMBER
OF SPECIFIED OR IMPLIED
COLUMNS
Explanation: The number of insert values in the value
list of the insert operation is not the same as the
number of object columns specified. Alternatively, the
number of values on the right side of an assignment in
a SET assignment statement or the SET clause of an
update operation does not match the number of
columns on the left side.
System action: The statement cannot be executed. No
data was inserted into the object table.
Programmer response: Correct the statement to
specify one and only one value for each of the specified
object columns.
SQLSTATE: 42802

Error: DB2 SQL Error: SQLCODE=-118, SQLSTATE=42902, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE OBJECT TABLE OR VIEW OF
THE DELETE OR UPDATE
STATEMENT IS ALSO IDENTIFIED IN
A FROM CLAUSE
Explanation: The table or view specified as the object
of a DELETE or UPDATE statement also appears in the
FROM clause of a subselect within the statement.
The table or view that is the object of a UPDATE or
DELETE cannot also be used to supply the values to be
inserted or to qualify the rows to be updated or
deleted.
System action: The statement cannot be executed. No
data was updated or deleted.
Programmer response: The implied function is not
supported by DB2. It may be possible to obtain the
desired result by creating a temporary copy of the
object table or view and addressing the subselect to
that copy. Refer to chapter 2 of SQL Reference for
information about the syntax of SQL statements.
SQLSTATE: 42902

Error: DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
A COLUMN OR EXPRESSION IN A
HAVING CLAUSE IS NOT VALID
Explanation: A column or expression identified in a
HAVING clause, possibly within a scalar function, does
not appear in the GROUP BY clause. Columns or
expressions specified in a HAVING clause must appear
within aggregate functions or also be specified in the
GROUP BY clause.
Grouping expressions can be used in a search condition
in a HAVING clause. A grouping expression specifies
only one value for each group. A grouping expression
specified in this context must exactly match a grouping
expression specified in the GROUP BY clause, with the
exception of blanks, which are not significant.
System action: The statement cannot be executed.
Programmer response: The implied function is not
supported by DB2. Refer to chapter 4 of SQL Reference
for information about the proper usage of HAVING
and GROUP BY clauses.
SQLSTATE: 42803

Above are list of DB2 SQL Errors and Warnings from Error -117 to -119 received while performing certain operation against DB2 Database or related products.

SQLCODE – Regardless of whether the application program provides an SQLCA or a stand-alone variable, SQLCODE is set by DB2 after each SQL statement is
executed. DB2 conforms to the ISO/ANSI SQL standard as follows:
If SQLCODE = 0, execution was successful.
If SQLCODE > 0, execution was successful with a warning.
If SQLCODE < 0, execution was not successful.
SQLCODE = 100, “no data” was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

SQLSTATE – SQLSTATE is also set by DB2 after the execution of each SQL statement. Thus, application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE.

With codeplex shutting down, we have moved SQL Nexus to github with a new release (6.0). Now both Pssdiag/SQLDiag manager and SQL Nexus are on github. Where to get it As you navigate to SQL Nexus, you can download code and released binary files. If you choose to download binary files, you can go to...

SQL Server : large RAM and DB Checkpointing Hi everyone, This post’s purpose is to establish a summary of the specific behaviors with relation to DB Checkpoint that may happen within SQL Server when running with a large quantity of allocated memory and when applicable, how to best address them. SQL Server 2016 improves...

Recently we got an inquiry from a customer who received the following message in errorlog and wanted to know why. [INFO] HkDatabaseTryAcquireUserMemory(): Database ID: [7]. Out of user memory quota: requested = 131200; available = 74641; quota = 34359738368; operation = 1. This is my first time to see this error. As usual, I relied...

Recently I assisted on a customer issue where customer wasn’t able to alter a memory optimized table with the following error Msg 41317, Level 16, State 3, Procedure ddl, Line 4 [Batch Start Line 35]A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases...

In a previous blog, I talked about memory optimized table consumes memory until end of the batch. In this blog, I want to make you aware of cardinality estimate of memory optimized table as we have had customers who called in for clarifications. By default memory optimized table variable behaves the same way as...

I worked on an interesting issue today where a user couldn’t restore a backup. Here is what this customer did: backed up a database from an on-premises server (2008 R2) copied the file to an Azure VM tried to restore the backup on the Azure VM (2008 R2 with exact same build#) But he got...

Recently, I looked an In-Memory OLTP issue with Principal Software Engineer Bob Dorr who is still my office neighbor. After restoring a database that had just one memory optimized table, we dropped the table. Even without any memory optimized tables,number of checkpoint files keep going up every time we issue a checkpoint. For a while,...

In this blog Added per-operator level performance stats for Query Processing, Senior PM in QP talks about extending operator level performance stats. They include stats related to reads, CPU and elapse time. These are very helpful to track down query performance issues. We worked on recent case where we put ActualElapsedms in a good...

In blog “Importance of choosing correct bucket count of hash indexes on a memory optimized table”, I talked about encountering performance issues with incorrect sized bucket count. I was actually investigating an out of memory issues with the following error. Msg 701, Level 17, State 103, Line 11There is insufficient system memory in resource pool...

I was working with a customer to troubleshoot memory optimized table issues. In this scenario, our customer uses a memory optimized table variable. He put 1 million rows of data into the table variable and then process it. Based on what he said, I tried to come up with a repro to see if...