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

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 – 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

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 Error Text Recreate Issue SQL Code Syntax Example Trouble Shooting Objective Let us determine the signatures for our referenced object, SYSPROC.SNAP_GET_DB. SQL Identify Routine Parameters Code Output Explanation ROUTINESCHEMA SYSPROC ROUTINENAME SNAP_GET_DB SPECIFICNAME SNAP_GET_DB SNAP_GET_DB_AP LANGUAGE C IMPLEMENTATION db2monudf!snap_get_db … Continue reading 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”