Lance Lambert of Blessed Memory

Videos

  1. The Cost of Intercession by Lance Lambert
    • Profile
      • Lance Lambert, of blessed memory, speaks at one of The Jerusalem Channel’s conferences on the cost of being an intercessor for Israel and the nations.
    • Videos

 

In-depth

The Cost of Intercession by Lance Lambert

  1. Intercessor
    • The word intercession has been greatly devalued
    • Many people come to me claiming that they are an intercessor
    • Many people are in prayer
  2. Real Intercessor
    • An intercessory is the deepest prayer
    • Journey to the throne of God
  3. World
    • This world is essentially a Spiritual World
    • You can not see Satan, but he exists
  4. Soul Versus Spiritual
    • It is not a spiritual thing
    • It is a soul thing
  5. Real Intercessory
    • Begins when God Reveals his mind
      • Abraham
        • I will destroy this nation, Sodom
      • Moses
        • I will destroy these people
  6. What is the mystery
    • Why
      • Why do we have to pray into it when God has said he will do it
      • We are a load of  Problems
      • Why do we have to be mindful
    • It is a good Question, and I
      • If you do not pray, I will not answer
      • If you do not intercede, I will not fulfill my purpose
    • Reasons
      • God seeks fellowship
      • Come into a place where we can read the mind of God
      • Educated and Trained for Eternal Purpose

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
 

Rav Daniel & Mirian Burg – “On Being Found”

Lyrics

Dear Evan Hansen

Even when the dark comes crashing through, you will be found
Like you could fall and no one hear
When you need a friend to carry you
When you are broken on the ground
Let the sun come streaming in
You will be found

 

Sermons

  1. Neighborhoods as Sacred Space (Beth Am & IFO)
    • Profiles
      • Beth Am Synagogue strives to be “In, For and Of” its neighborhood of Reservoir Hill in Baltimore, MD. See how relational work in an historically Jewish and African American neighborhood can soften boundaries, challenge assumptions and lift up positive stories of collaborative being.
    • Videos
      • Video #1
        Channel :- Beth Am Synagogue
        Published On :- 2018-April-15th
        Link
  2. On the Precipice of Violence (Rav Daniel Burg’s 10.27.18 Shabbat sermon)
    • Profiles
      1. Rav Daniel’s sermon Shabbat morning during the Pittsburgh shooting focused on potential and actual violence. At the conclusion of his remarks his wife Rabbi Miriam Burg, just informed of the Tree of Life Shooting, shares the news — which Rav Daniel then shares with the congregation. May the souls of our murdered brethren be bound up in the bonds of eternal of life, and may their memories be for a blessing.
    • Videos
      • Video #1
        Channel :- Beth Am Synagogue
        Published On :- 2018-Oct-27th
        Link

 

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 – 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 – LUW – Setting DB2_RESOURCE_POLICY to AUTOMATIC

Background

To reach for optimal system management one area that can help is having the Db/2 Instance’s DB2_RESOURCE_POLICY set to AUTOMATIC.

Script

Outline

  1. db2set
    • Set parameter
    • Review parameters
  2. Restart Instance
    • Stop Instance
    • Start Instance

db2set

Set

Code

db2set DB2_RESOURCE_POLICY=AUTOMATIC 

Review

Code

db2set

Output

DB2_RESOURCE_POLICY_20181001_1216AM.PNG

Restart Instance

Stop Instance

Code

db2 force applications all

db2 terminate

db2stop

Output

db2InstanceStop_20181001_1228AM

Start Instance

Code

db2start

Output
Image

db2InstanceStart_20181001_1231AM

Textual

 DB2-0 : The service has returned a service-specific error code.

10/01/2018 00:31:15     0   0   SQL1367N  Insufficient operating system configuration for resource policy support.
SQL1032N  No start database manager command was issued.  SQLSTATE=57019

 

Explanation

Unfortunately, we are not able to go forward.

Let us go see why.

 

Troubleshooting

SQL1367N

Googling on the error code SQL1367N landed us here:

SQL1367N
Link

Resource policy does not support the current operating system configuration.

Summary

Our targeted OS, MS Windows 2012 R2 Standard, is not one of the supported OSes for Db/2 Resource Policy Automatic provisioning.