SQL Error Code -305 & -803 in Program DSNTRIN After DSNTIJRT job run Case TS007245993

Hello Team,

• We are getting sqlid error code -305 & -803 in program DSNTRIN after submiiting DSNTIJRT job while applying Db2 mainteance.
  DSNTIJRT job run fine without any issue but in log we can see the errors.
  SELECT does not have a null-indicator for the SQLTYPEINFO column TYPE-NAME. That Table is used by Java.
  JOb output and MEPl report uploaded.
  Display ==> M (M=Msg,X=Hex) DB2 SSID ==> PD01
  SQLcode ==> -305 Opid ==> V991813 Planname ==> ?RRSAF
  SQLstate ==> 22002 Connid ==> RRSAF Contoken ==> 1AFCF4BA01D291A8
  Program ==> DSNTRIN Type ==> PKGE Stmt# ==> 23748
  Collid ==> DSNTRIN
  Version ==> UI69433
  
  -------------------------------------------------------------------------------
  
  DSNT408I SQLCODE = -305, ERROR: THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT
  HOST VARIABLE NUMBER 1 BECAUSE NO INDICATOR VARIABLE IS SPECIFIED
  DSNT418I SQLSTATE = 22002 SQLSTATE RETURN CODE
  DSNT415I SQLERRP = DSNXROHB SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD = -115 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
  DSNT416I SQLERRD = X'FFFFFF8D' X'00000000' X'00000000' X'FFFFFFFF'
  X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
  ******************************* BOTTOM OF DATA ********************************
  
  SELECT COLUMN_SIZE
  INTO :H
  FROM SYSIBM.SQLTYPEINFO
  WHERE TYPE_NAME = 'ARRAY'
  
  
  Display ==> M (M=Msg,X=Hex) DB2 SSID ==> PD01
  SQLcode ==> -803 Opid ==> V991813 Planname ==> ?RRSAF
  SQLstate ==> 23505 Connid ==> RRSAF Contoken ==> 1AFCF4BA01D291A8
  Program ==> DSNTRIN Type ==> PKGE Stmt# ==> 19903
  Collid ==> DSNTRIN
  Version ==> UI69433
  
  -------------------------------------------------------------------------------
  
  DSNT408I SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID
  BECAUSE INDEX IN INDEX SPACE TIX12768 CONSTRAINS COLUMNS OF THE
  TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS.
  RID OF EXISTING ROW IS X'0000002201'.
  DSNT418I SQLSTATE = 23505 SQLSTATE RETURN CODE
  DSNT415I SQLERRP = DSNXRINS SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD = -110 13172739 0 13817814 -490143744 0 SQL
  DIAGNOSTIC INFORMATION
  DSNT416I SQLERRD = X'FFFFFF92' X'00C90003' X'00000000' X'00D2D7D6'
  X'E2C90000' X'00000000' SQL DIAGNOSTIC INFORMATION
  ******************************* BOTTOM OF DATA ********************************
  
  
  INSERT
  INTO SESSION.OBJECT_ACCESS_LIST
  VALUES ( :H )After

After the analysis we got to know its normal and basically can be ignored .

They are really working as design.

E.g. for SQLCODE -803 DSNTRIN is creating a consolidated access list for an object.

To do this, DSNTRIN inserts the GRANTTO user IDs into the

SESSION.OBJECT_ACCESS_LIST table, which is defined with a unique index

key, to come up with a list of unique user IDs for the CGTT. Inserting

the duplicate user IDs causes SQLCODE -803.

So , you could accept these sqlcodes may in monitor,

IBM dev team has agreed that this is an internal query and that we're giving back the correct results so we're going to leave it as working as design.

We think otherwise :

any SQL Programmer would code an SQL Statement for a nullable column that can return a null value and trigger an error (-305).

The Programmer must either add a Null-Indicator to avoid the error, or he must use the COALESCE Function to assign a non-null Value in its place, avoiding the error.

Even if he is using the -305 to check if a null value exists, he could still assign a specific non-null value as default in the COALESCE that would help him identify that a null value has been found and avoid the error code.