Db/2 – Error – The insert or update value of the FOREIGN KEY is not equal to any value of the parent key of the parent table ( SQL Error [23503] )

Background

Wanted to go over an error I self generated so I can see the options available in Db/2 to address it.

Error

Error Image

SQLError_23503_20181105_0336AM.PNG

Error Text

SQL Error [23503]: The insert or update value of the FOREIGN KEY “bank.transaction.FK_bank_transaction_account” is not equal to any value of the parent key of the parent table.. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.21.29

Generate Error

Let us create the SQL Objects and trigger the problem.

Objective

We will have two tables, account and transactions.

The account’s table has a primary key, PK_bank_account.

The transaction’s table will have a primary key, PK_bank_transaction, as well.

The primary key will be a composite key on two columns, accountID and dt.

The transaction’s table will also have a Foreign Key, FK_bank_transaction_account.

The foreign key will reference the account table.

Our requirement is pretty basic each record in the transaction table must have a anchoring record in the Account’s table.

Data Model

Here is our Data Model

ERD_20181105_0451AM

DDL

account


CREATE TABLE "bank"."account"
(

	  "accountID"  		varchar(11) NOT NULL

	, "accountHolder"	varchar(60) NOT NULL

	,  "dtCreated"      TIMESTAMP   NOT NULL

)
//

ALTER TABLE  "bank"."account"

	ALTER COLUMN "dtCreated"

	SET DEFAULT CURRENT TIMESTAMP

//

ALTER TABLE "bank"."account"
ADD CONSTRAINT "PK_bank_account"
     PRIMARY KEY
     (
     	"accountID"
 	 )
//

transaction


CREATE TABLE "bank"."transaction"
(

	  "accountID"  		varchar(11)   NOT NULL

	, "dt"      		TIMESTAMP     NOT NULL

	, "amount"	        decimal(30,4) NOT NULL

)
//

ALTER TABLE  "bank"."transaction"

	ALTER COLUMN "dt"

	SET DEFAULT CURRENT TIMESTAMP

//

ALTER TABLE "bank"."transaction"
ADD CONSTRAINT "PK_bank_transaction"
     PRIMARY KEY
     (
     	   "accountID"
		,  "dt"
     )
//

ALTER TABLE "bank"."transaction"
ADD CONSTRAINT "FK_bank_transaction_account"
     FOREIGN KEY
     (
     	   "accountID"
     )
REFERENCES "bank"."account"

//

DML

Add a Transaction

SQL


INSERT INTO "bank"."transaction"
(

	  "accountID"
	, "dt"
	, "amount"

)
VALUES
	(
		  301
		, CURRENT_TIMESTAMP + 10 SECOND
		, 10.00
	)

//

Output

Output – Image

SQLError_23503_20181105_0634AM

Output – Textual

SQL Error [23503]: The insert or update value of the FOREIGN KEY “bank.transaction.FK_bank_transaction_account” is not equal to any value of the parent key of the parent table.. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.21.29

Output – Explanation

The key verbiage is the the one we are highlighted.

It reads that the entry we are trying to add does not have any value in the parent key of the parent table.

Remediation

Set Foreign Key to “Not Enforced”

Snippet

SQL

SQL – Syntax

ALTER TABLE [schema].[table]
     ALTER FOREIGN KEY [foreign-key]
     NOT ENFORCED
    ;

SQL – Sample

ALTER TABLE "bank"."transaction"
 	ALTER FOREIGN KEY "FK_bank_transaction_account"
 	NOT ENFORCED

Complete

Outline

  1. Alter Table – Alter Foreign Key – Not Enforced
  2. Add data to referencing table
  3. Add data to referenced table
  4. Alter Table – Alter Foreign Key – Enforced

SQL

SQL – Syntax

ALTER TABLE [schema].[table]
     ALTER FOREIGN KEY [foreign-key]
     NOT ENFORCED
    ;

/*
    Add records to dependent table
*/

/*
    Add records to referenced table
*/

ALTER TABLE [schema].[table]
     ALTER FOREIGN KEY [foreign-key]
     ENFORCED
    ;
SQL – Sample

ALTER TABLE "bank"."transaction"
 	ALTER FOREIGN KEY "FK_bank_transaction_account"
 	NOT ENFORCED
//	

INSERT INTO "bank"."transaction"
(

	  "accountID"
	, "dt"
	, "amount"

)
VALUES
	(
		  301
		, CURRENT_TIMESTAMP + 10 SECOND
		, 10.00
	)

//

INSERT INTO "bank"."account"
(
	  "accountID"
	, "accountHolder"
	, "dtCreated"

)
VALUES
	(
		  '301'
		, 'Joanne Crarford'
		, CURRENT_TIMESTAMP + 1 SECONDS
	)

//

ALTER TABLE "bank"."transaction"
 	ALTER FOREIGN KEY "FK_bank_transaction_account"
 	ENFORCED
 //	

ROLLBACK
//

 

Flag “Un-Enforced Constraints”

SQL

SQL – Sample


 SELECT 

		  CONCAT
		  	(
		  	    CONCAT
			  	(
			  		  rtrim(tblSTC.TABSCHEMA)
			  		, '.'
				)
				
			    , rtrim(tblSTC.TABNAME)
		    )
		  	AS "table"
			
		, tblSTC.CONSTNAME
			AS "constraint"
			
		, tblSTC.TYPE
			AS "type"
			
		, tblSTC.ENFORCED
			AS "enforced"
			
		, tblSTC.TRUSTED
			AS "trusted"
			
		, tblSTC.CHECKEXISTINGDATA
			AS "checkExistingData"
	
FROM   SYSCAT.TABCONST tblSTC

WHERE
		(
			   ( tblSTC.ENFORCED = 'N' )
			OR ( tblSTC.TRUSTED  = 'N' )
			OR ( tblSTC.CHECKEXISTINGDATA = 'N' )

		)

 

SQL – Output

unenforcedConstraints_bank_transaction_20181105_0822AM
 

Db/2 – SQL – Compound Statements Using Atomic Keyword

Background

Wanted to provide sample code on supporting so called “Compound Statements” in Db/2 LUW.

Definition

Here is IBM’s Definition:

A compound statement groups other statements together in an SQL routine. A compound statement allows the declaration of SQL variables, cursors, and condition handlers.

Outline

  1. Housekeeping
    • Db2 CLP
      • Revert Statement Terminator to ;
      • Turn off auto-commit
      • Enable Server Ouput
      • Change Statement Terminator
        • In our case we changed it to //
  2. Actual Code
    • Begin Atomic Block
      • Begin Atomic
    • Declare Variables
    • SQL Processing
    • End Atomic Block
      • Provide Statement Terminator
        • In our case //
  3. Environment Clean Up
    • DB2CLP
      • Revert Statement Terminator to ;
        • In our case we changed it to //
      • Disable Server Output

Code


--Set Terminator
--#SET TERMINATOR ;

-- Disable autocommit
UPDATE COMMAND OPTIONS USING C OFF;

--Set Server Output ON
SET SERVEROUTPUT ON;

--Change Terminator
--#SET TERMINATOR //

--Compound STATEMENT Begin
--Begin
BEGIN ATOMIC

    DECLARE lNumberofRecords                int;
    DECLARE lNumberofRecordsPostOperation   int;
    DECLARE lNumberofRecordsAffected        int;
    DECLARE strLog                          varchar(600);

    set lNumberofRecords = -1;
    set lNumberofRecordsAffected = -1;
    set lNumberofRecordsPostOperation  =-1;

    /*
        Get Current Number of Records
    */
    SET lNumberofRecords =
            (
                select count(*) 

                FROM    "bank"."balance"

            )           

    ;

    /*
        Display Number of Records
    */
    SET strLog =
                    TO_CHAR
                    (
                        coalesce
                        (
                            lNumberofRecords
                            , -1
                        )
                    )
                    CONCAT ' records are in table ( Original Count)'
                    ;

    CALL DBMS_OUTPUT.PUT_LINE(strLog);

    CALL DBMS_OUTPUT.NEW_LINE;  

    /*
        Remove All Records
    */
    DELETE "bank"."balance"
    ;   

    /*
        Get Number of Records by last operation
    */
    GET DIAGNOSTICS
        lNumberofRecordsAffected = ROW_COUNT
        ;

    SET strLog =
                    TO_CHAR(lNumberofRecordsAffected)
                    CONCAT ' record(s) pruned'
                    ;

    CALL DBMS_OUTPUT.PUT_LINE(strLog);

    CALL DBMS_OUTPUT.NEW_LINE;  

    /*
        Count Records
    */
    SET lNumberofRecordsPostOperation
            = (
                    select count(*)
                    FROM    "bank"."balance"
              )                 

    ;

    SET strLog =
                    TO_CHAR
                    (
                        coalesce
                        (
                              lNumberofRecordsPostOperation
                            , -1
                        )
                    )
                    CONCAT ' records are in table ( Post Operation )'
                    ;

    CALL DBMS_OUTPUT.PUT_LINE(strLog);

    CALL DBMS_OUTPUT.NEW_LINE;          

END

--Compound STATEMENT - Closing
//

--#SET TERMINATOR ;

SET SERVEROUTPUT OFF

 

Summary

Each SQL GUI ( IBM Db/2 CLP & IBM Data Studio, Dbeaver, DbVis, WinSQL ) will be slightly different in terms of customization.

But, wanted to provide a rough draft.

References

  1. IBM
    • DB/2
      • Home > Db2 for z/OS 10.0.0 > DB2 > SQL > SQL control statements for SQL routines
        • Compound-statement
          • Compound-statement
            Link

 

Db/2 – Locking – DBeaver

Background

Let us see how to stimulate a database blocking situation via DBeaver.

Steps

Outline

  1. Query 1
    • Start a new query window
    • Set commit mode to Manual Commit ( primary )
      • Manual or Auto-Commit
        • Ensure that Mode is set for Manual Commit
      • Issue Database Statement
        • Make sure that statement is not committed/rollback via “Explicit” reference
  2. Query 2
    • Start a new query window ( secondary )
    • Set commit mode to Manual Commit
      • Manual or Auto-Commit
        • Ensure that Mode is set for Manual Commit
      • Issue Database Statement
        • Make sure that statement is not committed/rollback via “Explicit” reference
  3. Review Transaction Log
    • List uncommitted queries

 

Steps

  1. Review Commit Mode ( Manual and Auto-Commit Mode ? )
    • Issue a new query window
    • From the tool bar confirm transaction commit mode
    • Make sure is is set to manual commit
  2. Query Window – 01
    • Create new query window
    • Enter Query
    • Execute Query
    • Make sure query does not contain explicit commit nor rollback clause
  3. Query Window – 02
    • Create new query window
    • Enter Query
    • Execute Query
    • Make sure query does not contain explicit commit nor rollback clause
  4. Review Transaction Log
    • Invoke menu “Database/Transaction Log”
    • Transaction Log
      • The “Transaction Log” window opens
      • Review list of uncommitted transactions

 

Images

Manual/Auto Commit Mode

Auto-Commit Mode

transactionCommitMode.Auto.20181024.1029AM.PNG

Changing Mode

transactionCommitMode.Reviewing.20181024.1030AM.PNGl

Mode Changed to Manual

transactionCommitMode.Manual.20181024.1031AM.PNG

Query 1

Toolbar

toolbar.01.20181024.0953AM.PNG

Query Window – 01

Query Entered into Query Window 01

toolbar.02.setMode.20181024.0957AM.PNG

Query Executed against Query Window 01

toolbar.03.queryExecuted.20181024.0958AM.PNG

Query Window – 02

Query Entered into Query Window 02
toolbar.03.query02.BeforeExecution.20181024.0959AM.PNG
Query Executed against Query Window 02

toolbar.03.query02.AfterExecution.20181024.1000AM.PNG

Transaction Log

Review Transaction Log

transactionLog.02.20181024.1001AM.PNG

Db/2 – Locking – Using Db2CMD

Background

Wanted to see what it will take to create a blocking situation in DB/2 LUW.

Scenario

Outline

  1. Create Table
  2. Add Data
  3. Update Data
    • Setup for no commit
      • SQL
        • update command options using c off;
    • Payload
      • Update data, but do not commit
      • Update data

Processing

Create Table

CREATE TABLE "bank"."balance"
(
	  "id" int NOT NULL
	, "balance" DECIMAL(30,2) NOT NULL

)
//

ALTER TABLE "bank"."balance"
ADD CONSTRAINT "PK_BANK_BALANCE"
PRIMARY KEY
(
	"id"
)
//

COMMIT
//

Add Data

DELETE
FROM   "bank"."balance"
//

INSERT INTO "bank"."balance"
(
	  "id"
	, "balance"
)
VALUES
	  ( 1, 1 )
	, ( 2, 2)
	, ( 3, 3)
	, ( 4, 4)
	, ( 5, 5)
//

COMMIT

Update Data

Update Data -01

connect to WIDEWRLD;

update command options using c off;

SET ISOLATION LEVEL RR;

UPDATE "bank"."balance" SET "balance" = "balance" + 0.001 WHERE "id" = 5 WITH RR;

Output -01

session_01_20181223_1215PM

Update Data -02

connect to WIDEWRLD;

update command options using c off;

SET ISOLATION LEVEL RR;

UPDATE "bank"."balance" SET "balance" = "balance" + 0.001 WHERE "id" = 5 WITH RR;

Output -02

session_02_20181223_12175PM

Summary

During our updates, we set “auto commit off“, and we intentionally did not commit our session.

We opened up a new session window and tried updating the same record.

We observed that our second session never completed.

Db/2 LUW – Listening Port Using OS Tools

Background

Wanted to quickly review network ports that are being used by our DB/2 Instance.

Outline

  1. netstat
  2. lsof

Tools

netstat

Sample


netstat -anp | grep LISTEN | grep -i DB2

Output

netstat_linux_20181006_1141AM

lsof

Syntax


lsof

Sample – Usage


lsof -i | grep -i 'TCP' | grep 'LISTEN'

Output

lsof.linux.20181006.1201PM

Sample – Get Title


lsof 2>&1 | head -n 1

Output

lsof.linux.head.20181006.1159AM

Db/2 – Error – “SQL Error [42725]: Reference to routine was made without a signature, but the routine is not unique in its schema.. SQLCODE=-476”

Background

Trying to manage a routine and ended up with the error listed below.

Error

Error Image

SQLError.42725.SYSPROC.SNAP_GET_DB.20181013.0859PM

Error Text


SQL Error [42725]: Reference to routine "SYSPROC.SNAP_GET_DB" was made without a signature, but the routine is not unique in its schema.. SQLCODE=-476

Recreate Issue

SQL

Code

Syntax


GRANT EXECUTE ON FUNCTION [schema].[function] TO [principal]

Example


GRANT EXECUTE ON FUNCTION SYSPROC.SNAP_GET_DB TO DADENIJI

Trouble Shooting

Objective

Let us determine the signatures for our referenced object, SYSPROC.SNAP_GET_DB.

SQL

Identify Routine Parameters

Code


WITH cteRoutineParm
AS
(

    SELECT 

              tblRP.ROUTINESCHEMA
            , tblRP.ROUTINENAME
            , tblRP.SPECIFICNAME    

            , LISTAGG
                (
                      CASE 

                        WHEN (

                                tblRP.TYPENAME
                                    IN (
                                           'CHAR'
                                         , 'VARCHAR'
                                       )
                            )

                                THEN tblRP.TYPENAME
                                    CONCAT ' ('
                                    CONCAT tblRP.LENGTH
                                    CONCAT ' )'

                        ELSE tblRP.TYPENAME

                      END       

                    , ', '
                )
              WITHIN GROUP
              (
                ORDER BY

                    tblRP.ORDINAL
              )

              AS "type"         

    FROM   SYSCAT.ROUTINEPARMS tblRP

    WHERE  ROWTYPE = 'P'

    GROUP BY

              tblRP.ROUTINESCHEMA
            , tblRP.ROUTINENAME
            , tblRP.SPECIFICNAME        

)   

SELECT
              tblSS.ROUTINESCHEMA
            , tblSS.ROUTINENAME
            , tblSS.SPECIFICNAME
            , tblSS.LANGUAGE
            , tblSS.IMPLEMENTATION
            , tblSS.PARM_COUNT
            , cteRP."type"

FROM SYSCAT.ROUTINES tblSS

INNER JOIN cteRoutineParm cteRP 

        ON tblSS.ROUTINESCHEMA = cteRP.ROUTINESCHEMA
        AND tblSS.ROUTINENAME  = cteRP.ROUTINENAME
        AND tblSS.SPECIFICNAME = cteRP.SPECIFICNAME 

WHERE tblSS.ROUTINESCHEMA  IN
        (
            'SYSPROC'
        )

AND tblSS.ROUTINENAME IN
        (
            'SNAP_GET_DB'
        )

ORDER BY
          1
        , 2
        , 3<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Output

SYSPROC.SNAP_GET_DB.20181014.1213PM.PNG

Explanation

  1. ROUTINESCHEMA
    • SYSPROC
  2. ROUTINENAME
    • SNAP_GET_DB
  3. SPECIFICNAME
    • SNAP_GET_DB
    • SNAP_GET_DB_AP
  4. LANGUAGE
    • C
  5. IMPLEMENTATION
    • db2monudf!snap_get_db
    • db2monudf!snap_get_db_ap
  6. PARAMETER COUNT
    • 2
    • 1
  7. PARAMETER TYPE
    • VARCHAR, INTEGER
    • VARCHAR

Remediation

Objective

Let us determine the signatures for our referenced object, SYSPROC.SNAP_GET_DB.

SQL

Use Specific Name

Outline

Use Specific Name.

Code

Syntax

GRANT EXECUTE ON SPECIFIC FUNCTION [schema].[function] TO principal

Actual

GRANT EXECUTE ON SPECIFIC FUNCTION SYSPROC.SNAP_GET_DB TO DADENIJI

Use Function Signature

Outline

Use Specific Name.

Code

Syntax

GRANT EXECUTE ON FUNCTION [schema].[function](parmtype1, parmtype2)<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span> TO principal

Actual

GRANT EXECUTE ON FUNCTION SYSPROC.SNAP_GET_DB (VARCHAR(255), INTEGER) TO DADENIJI

Db/2 – PowerShell – Lab Exercises – 01

Background

Wanted to start placing together sample Lab Exercises for working with Db/2 and PowerShell.

Prerequisites

Please make sure you have IBM’s Data Server Provider for .NET installed.

 

Code


#set provider invariant name
$dbProviderInvariantName = "IBM.Data.DB2";

#Get Database Provider Factory
$factory = [System.Data.Common.DbProviderFactories]::GetFactory($dbProviderInvariantName);

#Initialize Database ConnectionString
$cstrbld = $factory.CreateConnectionStringBuilder();

#Set Database ConnectionString
$cstrbld.Database = "WIDEWRLD"
$cstrbld.UserID = "dadeniji"
$cstrbld.Password = "pass2"
$cstrbld.Server = "localhost:50000"

#Initialize Database Connection Object
$dbconn = $factory.CreateConnection()

#Set Database Connection Object ConnectionString
$dbconn.ConnectionString = $cstrbld.ConnectionString

# Open Database Connection
$dbconn.Open()

# Create Database Command Object
$dbcmd = $factory.CreateCommand();

# Set Database Command Connection Property to DB Connection
$dbcmd.Connection = $dbconn

$sql = @"
		SELECT
			  tbl.CATALOG_PARTITION_NAME AS partition
			, tbl.DB_NAME AS db
			, tbl.SERVER_PLATFORM AS platform

		FROM TABLE (
				SNAP_GET_DB
				(
					''
					, 0
				)
			) AS tbl
"@

$dbcmd.CommandText = $sql;

#set Command Type Text
$dbcmd.CommandType = [System.Data.CommandType]::Text

# Create Data Adapter
$dataAdapter = $factory.CreateDataAdapter()

# Set Data Adapter :- Select Command
$dataAdapter.SelectCommand = $dbcmd

# Create Dataset Object
$dataSet = New-Object System.Data.DataSet

# Fill Data Adapter with Dataset Object
$dataAdapter.Fill($dataSet)

# Set Data Table
$dataTable = $dataSet.Tables[0];

# Show Data Table as Grid
$dataTable | Out-GridView;

# Close Database Connection
$dbconn.Close()

#Wait
$HOST.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") | OUT-NULL;
$HOST.UI.RawUI.Flushinputbuffer();

Output

db2_powershell_01