IBM Divestiture – 2019

Background

IBM recently completed its purchase of Raleigh’s North Carolina Linux Powerhouse, Red Hat.

As part of that corporate merger, it appears regulars whether here overseas are concerned of allowing too much vertical technology ( Application software, Operating System, and Integration Software ) to rest in a single entity.

 

Affected Product Lines

HCL Software

  1. Collaboration
    • Notes Domino
  2. Application Security
    • AppScan
  3. Application Delivery
    • Bigfix
  4. Marketing
    • Unica
  5. Digital Experience

 

Impact

The current customer base of the products will obviously be affected.

Impacts will include license renewals, support issue tracking, etc.

Customer facing support staff will also have to respond graciously concerns raised by customers.

Referenced Material

  1. HCL
    • HCL TECHNOLOGIES ANNOUNCES CLOSE OF ACQUISITION OF SELECT IBM PRODUCTS
      Link

 

Eclipse IDE – Installation On MS Windows

Background

I have a need to review some of our applications configuration and interaction with our back end database.

In this case we will use Eclipse as a client development tool; and use MS SQL Server as our back-end database engine.

Eclipse IDE

Artifacts

Installer

Eclipse Installer are available here :-

Image

artifact.20190504.0449PM

Timeline

As of 2019-April, the current installer is 2019-03R.

Supported OS

Supported OSes are :-

  1. Mac OS X
  2. Windows
  3. Linux

Unfortunately, the only bitness supported is 64-bit.

32-bit is no longer supported.

Tool

The are various packages that are available.

We will be developing in the Java Language and will like to consider ourselves as Enterprise developer and so will chose “Eclipse IDE for Enterprise Java Developers”.

Others who are not targeting J2EE types of applications can likely getaway with “Eclipse IDE for Java Developers”.

Image

artifact.package.20190504.0456PM.PNG

Download

We chose “Eclipse IDE for Enterprise Java Developers” and Windows 64-bit as our targeted OS is MS Windows x64.

Installation

Steps

  1. Launch the installer
  2. The “User Account Control” appears
    • Please click “Yes” to continue
  3. Installation Folder
    • Please choose the folder to target
  4. The “Eclipse Foundation Software User Agreement” window appear
    • Please indicate acceptance of the Agreement
  5. Certificates
    • Please choose to accept the Certificates

Images

User Account Control

install.userAccessControl.20190504.0107PM.PNG

Eclipse IDE for Enterprise Java Developers

install.eclipseInstaller.01.20190504.0111PM.PNG

Installation Folder

install.eclipseInstaller.installationFolder.01.20190504.0111PM.PNG

Eclipse Foundation Software User Agreement

install.eclipseInstaller.EULA.01.20190504.0114PM.PNG

Installing

Installing -01

install.eclipseInstaller.installing.01.20190504.0114PM.PNG

Licenses

Licenses -01

install.licenses.EULA.01.20190504.0115PM.PNG

Installing

Installing -02

install.eclipseInstaller.installing.02.20190504.0117PM.PNG

Installing

Certificates -01

install.eclipseInstaller.certificates.01.20190504.0120PM.PNG

Certificates -02

install.eclipseInstaller.certificates.02.20190504.0120PM.PNG

Usage

Once installation is completed, please start the application.

Dedicated

Dedicated to IBM.

In 2001, developed as a replacement to Visual Age.

17 years and running!

Wikipedia

Link

  1. Name
    • According to Lee Nackman, Chief Technology Officer of IBM’s Rational division (originating in 2003) at that time, the name “Eclipse” (dating from at least 2001) was not a wordplay on Sun Microsystems, as the product’s primary competition at the time of naming was Microsoft Visual Studio, which Eclipse was to eclipse.
  2. History
    • Eclipse was inspired by the Smalltalk-based VisualAge family of integrated development environment (IDE) products.
    • Although fairly successful, a major drawback of the VisualAge products was that developed code was not in a component-based software engineering model.
    • Instead, all code for a project was held in a compressed lump (somewhat like a zip file but in a proprietary format called .dat).
    • Individual classes could not be easily accessed, certainly not outside the tool.
    • A team primarily at the IBM Cary NC lab developed the new product as a Java-based replacement.
    • In November 2001, a consortium was formed with a board of stewards to further the development of Eclipse as open-source software. It is estimated that IBM had already invested nearly $40 million by that time.
    • The original members were Borland, IBM, Merant, QNX Software Systems, Rational Software, Red Hat, SuSE, TogetherSoft, and WebGain.
    • The number of stewards increased to over 80 by the end of 2003. In January 2004, the Eclipse Foundation was created.

Summary

Good things can happen and stay happening when people come together.

In follow-up posts, we will develop simple applications.

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 – CLP – Case Sensitivity

Background

Playing around with Db/2 Command Line and ended up with an interesting insight.

Query

Here is the query :-


db2 select count(*) from Dimension.Date

Error

Error Text


SQL0204N  "DIMENSION.DATE" is an undefined name.  SQLSTATE=42704

Error Image

SQLError.SQL0204N.20181027.1141AM

Remediation

Escape Object Name

Objective

Please quote object names by enclosing the schema and table in double quotes.

And, escape the double quotes with a back slash.

SQL


db2 select count(*) from \"Dimension\".\"Date\"

Output

SQLError.SQL0204N.20181027.1145AM

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.