ANSI Standards – Database – Substring

Background Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms. Data Our test table is Oracle's SCOTT.EMP Here is what the data looks like :- Substring/Substr The HIREDATE is the only well formatted fixed length string. Its format is DD-MMM-YY. Query Oracle SQL Output Explanation Oracle relies on Substr to … Continue reading ANSI Standards – Database – Substring

Database – Buffer Pool – Clearing

Background During Database performance stressing, data is ingested into memory and written to disk. As one tries out different database table structures and Data Manipulation Languages ( DML ) queries, one often finds it useful to clean the slate in between.   Granularity Like any other operation one can target processing against the server, database, … Continue reading Database – Buffer Pool – Clearing

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 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, … Continue reading 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] )

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 :- Error Error Text Error Image 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 Output

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 Housekeeping Db2 CLP Revert Statement Terminator to ; Turn off auto-commit … Continue reading Db/2 – SQL – Compound Statements Using Atomic Keyword

Db/2 – Locking – DBeaver

Background Let us see how to stimulate a database blocking situation via DBeaver. Steps Outline 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 … Continue reading Db/2 – Locking – DBeaver

Db/2 – Locking – Using Db2CMD

Background Wanted to see what it will take to create a blocking situation in DB/2 LUW. Scenario Outline Create Table Add Data 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 Add Data Update Data Update Data -01 Output … Continue reading Db/2 – Locking – Using Db2CMD