Db/2 LUW – Conditional Schema Creation

Background

Wanted to explore what it will take to conditional create a schema in DB/2 LUW.

Transact SQL

In Transact SQL, it is straight forward.


if schema_id('hr') is null
begin

   exec('create schema [hr] authorization [dbo]');

end

DB2

Stored Procedure

ddlCreateSchema

Outline

  1. Declaration
    • Language :- SQL
    • Atomic
  2. Code
    • Find matching Schema in SYSCAT.SCHEMATA
    • If none found, then create schema
      • sql is ‘CREATE SCHEMA ‘ || v_SchemaQuoted
      • Prepare SQL String
        • Convert String to Prepared Statement
      • Execute Prepared Statement

Code

CREATE OR REPLACE PROCEDURE ddlCreateSchema
(
    vSchema VARCHAR(128)
)
SPECIFIC ddlCreateSchema
LANGUAGE SQL
BEGIN ATOMIC

    DECLARE v_count         INT;
    DECLARE v_buffer        VARCHAR(600);
    DECLARE v_SQLProcessed  BOOLEAN;

    DECLARE v_sql           varchar(4000);

    DECLARE v_SchemaQuoted   varchar(128);

    DECLARE CHAR_SINGLE_QUOTE  char(1);
    DECLARE CHAR_DOUBLE_QUOTE  char(1);

    DECLARE v_statement     statement;

    SET CHAR_SINGLE_QUOTE = '''';
    SET CHAR_DOUBLE_QUOTE = '"';

    SET v_SQLProcessed = FALSE;

    /*
     * Add Double Quotes to ensure case kept
    */
    SET v_SchemaQuoted = CHAR_DOUBLE_QUOTE
                            || vSchema
                            || CHAR_DOUBLE_QUOTE
                            ;

    /*
     * Get Number of Matching Schemas
     */
    SET v_count =  (
                        SELECT COUNT(*)
                        FROM   SYSCAT.SCHEMATA
                        WHERE  SCHEMANAME = vSchema
                   )
                    ;   

    SET v_buffer = 'Number of matching schema ';
    SET v_buffer = v_buffer || cast( v_count AS varchar(3));

    CALL DBMS_OUTPUT.PUT_LINE(v_buffer);

    /* If count is 0, then create schema
    */
    IF (v_Count = 0) THEN

        -- EXECUTE IMMEDIATE(v_sql);
        SET v_sql = 'CREATE SCHEMA ' || v_SchemaQuoted;

        PREPARE v_statement FROM v_sql;

        EXECUTE v_statement;

        SET v_SQLProcessed = TRUE;

    END IF;

END

//

Invoke


BEGIN ATOMIC

    DECLARE vSchema varchar(128);

    SET vSchema = 'hr';
    --SET vSchema = 'hr2';

    CALL DDLCREATESCHEMA(vSchema);

END

//

Summary

Noticed the following

  1. In Oracle & Db/2
    • Object Names are converted to upper-case unless quoted in double-quotes
  2. The following are only available within programmable objects
    • Datatype
      • Boolean
    • Statements
      • Prepare
      • Execute

References

  1. IBM Knowledge Center
    • Home > DB2 for Linux UNIX and Windows 9.7.0 > Product overviews > DB2 Database for Linux, UNIX, and Windows > What’s New overview > New features and functionality > SQL Procedural Language (SQL PL) enhancements > New data types are supported
  2. Serge Rielau
    • Developer Works
      • Using dynamic SQL inside SQL PL
        Link
  3. Antonio Maranhao
    • Developer Works
      • Execute Immediate
        Link
  4. Stack Overflow
    • How to execute an SQL string in DB2
      Link
    • Create table if not exists syntax db2
      Link

Dbeaver :- Editor – Customization – “Statement Delimiter”

Background

Got around to developing programmable objects, Stored Procedures and Functions, with DBeaver.

Error

Ran into an error as the routine, specifically function, got a bit more complex.

Image

endofStatement_error_01_20180912_0257AM.PNG

Textual


 SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "
        end if".  Expected tokens may include:  "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Trouble Shooting

The error seems to be related to statements that span multiple lines such as :

  1. if/then/end if
  2. while do / end while

 

Remediation

Outline

  1. Configuration
    • Access Menu
      • Window > Preferences > Editors/SQL Editor/SQL Processing
        • Statement Delimiter
          • Change Statements delimiter: from “;” to something peculiar
            • We are using //
        • Ignore Native Delimiter
          • Enabled/Checked
        • Blank line is statement delimiter
          • Enabled/Checked
  2. SQL Editor
    • Within the text pane
      • Please end your programmable object code definition with the delimiter

 

Configuration

Image

Origin

endofStatement_customize_01_20180912_0302AM.PNG

Revised

endofStatement_customize_02_20180912_0329AM

 

SQL Editor

Image

Revised

codeEditor_20180912_0336AM

 

Dedicated

Anyone who knows me, know I do not keep opinions to myself.

It took a long time, too much.

Sorry, but not in any mood to waste even more time.

I just spent 30 minutes listening in to Wendy Williams talk about people throwing shoes at each other; knowing we are better than this.

So here we go.

Dedicating to people worth talking about, people what applauding.

Serge Rider, the great Sergent!

Conversations Tracked

MySQL DELIMITER statement
Link

  1. erseco
    • In English
      • It would be interesting support the MySQL DELIMITER statement to expedite the development and testing of stored procedures and functions.Thanks!
    • In spanish
      • Sería interesante soportar el DELIMITER de MySQL para agilizar el desarrollo y testeo de funciones y procedimientos almacenados.
  2. Serge
    • Hello, Yes, we are going to add this feature (not only for MySQL). Generally it’s quite not easy feature (as we need to make complex analysis of SQL script) but hopefully it’ll be implemented soon.  Thanks!
  3. Endo
    • Any update?
  4. carlosspohr » Fri Mar 27, 2015 3:37 am
    • It will beautiful when implemented on DBeaver!
      @Serge, I can test this feature for mysql db if you want…every day I need to use cmd or terminal to run my procedures coded in DBeaver 🙂
  5. guni82
    • Hi!
      Setting the delimiter would be a great and essential feature!
      I’m using triggers and procedures in Firebird a lot and can’t import them from a script at the moment.
      I could even live with an option to change the standard delimiter from ; to something else.
      There is an option called Script Delimiter in Avanced Firebird Driver Parameters. It activates e.g. $$ as a delimiter but unfortunately doesn’t deactivate ;
      However, congrats to your great work!
      Thanks
  6. Mikemcg
    • Just discovered DBeaver and it is best tool of this kind I have used. However the lack of support for DELIMITER in MySQL means I can’t run scripts to create Stored Procedures so I still need to revert to other tools when I would prefer to just use this one.

      Any fixes or workaround much appreciated.

  7. Serge
    • Priority of this feature was increased, hopefully next version of DBeaver will have support of custom delimiters.
  8. Serge
    • Version 3.4.0 supports custom script delimiters:
  9. aleishman » Tue Dec 01, 2015 2:25 pm
    • Your tool is absolutely awesome but the delimiter thing is a serious issue. Being ablt to set and change the delimiter at will in a long script file is extremely important
  10. tb_ai » Sat Jan 09, 2016 10:27 pm
    •  

      Like Serge said, the new version can solve the delimiter problem (workaround solution).
      Window > Preferences > Editors/SQL Editor/SQL Processing:
      change Statements delimiter: from “;” to “~” (DBeaver only allow 1 char, so I pick “~”)

      You need to remove the delimiter statement and replace your delimiter with “~”

      I was using mySQL workbench but after updating my CentOS 7, the resultgrid is no long showing. So I switch to DBeaver and it works very well.

 

References

  1. Dbeaver
    • Dbeaver Forum
      • MySQL DELIMITER statement
        Link
    • Dbeaver Community
  2. IBM
    • Home > Db2 for z/OS 11.0.0 > Db2 codes > SQL codes
      • SQL error codes
        Link

Win OS – Run Application has an Administrator

Background

In a previous post, “Data Studio – Error – ‘configuration\org.eclipse.osgi.manager.fileTableLock (Access is denied.)’ ”, spoke of the need to run an application as an Administrator.

That post is here.

Always “Run As An Administrator

Let us see what it will take to always run an Application as an Administrator.

Guide

Googled on our title and found a very worthy post.

It is by “Sarah Jacobsson Purewal” and it is titled “Always run a program in administrator mode in Windows 10” as our guide.

That post is here.

Steps

Outline

  1. Identify Application Launcher
  2. Review Application Launcher
    • Review Application’s folder and binary name
  3. Change Application’s compatibility mode
    • Change “Run as an Administrator” mode for all users

Identify Application Launcher

Search for Application

In Windows Desktop, please search for the Application.

In our case “IBM Data Studio“.

desktop_datastudio_properties_20180830_0848AM.PNG

Open file Location

Please right click on the app and the drop-down menu appears.

From the drop-down menu, please choose “Open File Location“.

desktop_datastudio_openfilelocation_20180830_0853AM

Review Application Launcher

Start Menu \ Programs \ [Application]

Here is what the start menu for our application looks like.

startmenu_programs_ibmdatastudio_20180830_0903AM.PNG

Start Menu \ Programs \ [Application] \ [shortcut]

Let us access the short cut that we want to dig into.

Data Studio 4.1.3.3 Client
Properties

We selected the shortcut and right clicked on it.

Here is what our drop-down looks like.

startmenu_programs_ibmdatastudio_properties_dropdown_20180830_0910AM

Shortcut – Image

startmenu_datastudio_properties_shortcut_20180830_0914AM.PNG

Shortcut -Textual
  1. Target :- “E:\Program Files\IBM\DS4.1.3\eclipse.exe” -product com.ibm.datastudio.consolidated.product.ide
  2. Start In :- “E:\Program Files\IBM\DS4.1.3”

 

Application’s compatibility mode

Our application’s full name is “E:\Program Files\IBM\DS4.1.3\eclipse.exe”.

Let us go set its compatibility mode.

Windows Explorer

Using Windows Explorer, we accessed our application’s folder, E:\Program Files\IBM\DS4.1.3\.

windowsExplorer_IBMDataStudio_20180830_0926AM

 

Windows Explorer – Application

We selected our application and right clicked on it.

windowsExplorer_IBMDataStudio_properties_20180830_0928AM

Properties

Properties – Tab – General

Here is the initial tab when the Properties for an Application is accessed.

Properties_eclipse.exe_tab_general_20180830.0934AM

Properties – Tab – Compatibility

Please access the Compatibility tab.

Properties_eclipse.exe_tab_compatibility_01_20180830.0937AM

Please click on the “Change Setting for all users” button.

Properties – Tab – Compatibility – Change Settings for all users

Here is what the “Change Settings for all users” window look like.

Properties_eclipse.exe_tab_compatibility_changeSettigforallusers_01_20180830.0941AM

Please make the changes identified below:

  1. Run this program as an Administrator

Here is a screen that reflects our change.

eclipse.exe.compatibilityforallusers.20180830.0945AM

 

Dedication

Dedicated to Sarah Jacobsson Purewal.

SQLQueryStress – Parameter Substitution

Background

A quick follow-up to our last post on SQLQueryStress.

BTW, that post is here.

Parameter Substitution

Query

Original Query

Code

insert into [dbo].[activity]
default values

Revised Query

Code

select
          tblP.BusinessEntityID
        , tblP.FirstName
        , tblP.LastName
        , tblP.MiddleName

from  [Person].[Person] tblP

where
        (
                ( tblP.LastName = @lname )
            and ( tblP.FirstName = @fname )
        )

Code – Explanation

In the query above, we have two arguments @lname and @fname.

Substitution

Outline
  1. Click the “Parameter Substitution” button
  2. In the “Parameter Substitution” window
    • Click the “Database” button to set the Database that the parameter arguments reside on
    • In the “Parameter Query” textbox, enter a query that will fetch the parameter arguments
    • Click the “Get Columns” button to have the Database return the list of columns that will be returned from running the Query
    • Match each parameter to its corresponding database column
Images
Image – Parameter Substitution

Click on the “Parameter Substitution” button.

parameterSubstitution__Initiate__20180821_1126AM
Image – Parameter Query

Enter the query that will fetch values for all the data-set that will feed our parameters.

parameterSubstitution__ParameterQuery_20180821_1102AM.PNG

Image – Get Columns – Get Columns

Click on the “Get Columns” button.

Clicking on the “Get Columns” button generates the column names from the Parameter Query.

parameterSubstitution__GetColumns_Before_20180821_1104AM.PNG

Image – Get Columns – Align

Map parameters with column names.

parameterSubstitution__GetColumns_After_20180821_1105AM.PNG

 

Load

SQL Profiler

Images

Image 01

payload_20180821_1210PM_01.PNG

Image 02

payload_20180821_1211PM_02.PNG

Explanation

We can observe variability in the RPC:Completed Event captured for each iteration.

SQLQueryStress

Background

Performance Issues & Load Stressing Exercises are never faraway ideas from a DBA thoughts.

Let us try out SQLQueryStress.

 

SQLQueryStress

Lineage

SQLQueryStress born out of work by Adam Machanic.

Now maintained by Erik Ejlskov Jensen ( ErikEJ ).

 

Artifacts

The source code for SQLQueryStress is available from ErikEJ’s GitHub site.

Here is the URL :-

ErikEJ/SqlQueryStress
Link

Download

Please download the source code from the GitHub site:

download_20180820_1107PM

Build

Please launch Visual Studio.

The community edition V2017 is the one that I have.

Access the Source Code’s solution file and  build the solution.

 

Deliverable

Once built we will access the bin\Debug for the executable ( SQLQueryStress.exe )

build_debug_20180820_1114PM

 

Usage

Outline

  1. Please click the Database button to enter Database specification details
    • Server
    • Authentication
      • Integrated Authentication
      • SQL Server Authentication
    • Database
  2. Load Details
    • Number of Iterations
    • Number of Threads

Database

database_20180820_1120PM

Entry

Load Settings

entry_20180821_1232AM

SQL Query

SQLQueryStress_Usage_Perform_20180821_0836AM

Execute Test

Please press GO to execute the test.

 

Review Results

SQLQueryStress_Query0N_Completed_Exceptions_20180821_0907PM.PNG

Profiling Java Apps Using Bundled Tools

Background

Lately I have found myself playing with a lot of Applications that are running inside Java’s JVM.

The responsible next step is to start profiling their consumption patterns.

 

Referenced Work

These days most of my work are begun from a Google Search.

This work started from Karun Subramanian’s well thought effort.

Here are his blog posts:

  1. 5 not so easy ways to monitor the Heap Usage of your Java Application
    Link

 

Constraints

The tools that we will be evaluating have the following constraints:

  1. They should be run under the same account as the Java Processes that will be monitored

 

Tools

In this exercise we will look at our tools from the prism of Interface; Command Line  Interface ( CLI ) or Graphical User Interface.

Outline

  1. Interface
    • Command Line Tools ( CLI )
      • jstat
      • jmap
    • GUI
      • visualvm
      • jconsole

Preparation

jps

Outline

jps is analogous to the ps command in Linux.

While ps list all processes; jps lists all running Java Processes.

 

Syntax


jps

 

Sample


jps | find /I /V "Jps"

 

Output

 

Explanation

  1. The first column is the LVMDID ( process ID)
  2. The second column is the Process Name

 

Command Line Interface ( CLI )

jstat

Artifacts

Upon installing Java’s JDK, jstat is available in the JDK’s HOME bin folder.

Code

syntax

jstat -gc [process-id]

Sample

jstat -gc 13800

Output

jstat_20180811_0913AM

Explanation
Column Meaning Sample
EC Eden Capacity (KB) 69952
EU Eden Space Utilization (KB) 19112.8
OU Old Space Utilization (KB) 120747.8
OC Old Space Capacity (KB) 174784.0

jmap

Artifacts

jmap is available in the JDK’s HOME bin folder.

Code

syntax

jmap -heap [process-id]

Sample

jmap -heap 13800

Output

es.20180811.0943AM

Explanation
Attribute Meaning Sample
MaxHeapSize Maximum Heap Size 256 MB
NewSize Eden Space Utilization 85 MB
MaxNewSize Eden Space Capacity 85 MB
OldSize Old Space Utilization 170 MB

jhsdb

Artifacts

From Java JDK v9, jhsdb is available in the JDK’s HOME bin folder.

Code

syntax

jhsdb jmap -heap --pid [process-id]

Sample

jhsdb jmap --heap --pid 1960

Output

Explanation
Attribute Meaning Sample
G1
Regions 2020
Capacity 2020 MB ( 2 GB )
Used 98 MB
Free 1921 MB ( 1.92 GB)
Used % 4.90%
G1 – Eden Space
Regions 62
Capacity 74 MB
Used 62 MB
Free 12 MB
Used % 83.78%
G1 – Survivor Space
Regions 4
Capacity 4 MB
Used 4 MB
Free 0
Used % 100 %
G1 Old Generation
Regions 34
Capacity 50 MB
Used 32 MB
Free 17 MB
Used % 65.79 %

 

Graphical User Interface ( GUI )

visualVM

Artifacts

Java Version
Java Version v1.8 and Before

Up to Java v1.8 Visual M was bundled with Java JDK.

Once Java’s JDK is installed, please access visualvm.exe from the JDK’s HOME bin folder.

Java Version  9 and Above

Please download VisualVM from here.

Extract the zip file and run visualvm.exe from the bin folder.

Visual

Tab – Overview
Image

es_Tab_Overview_20180811_1004AM.PNG

Textual
  1. JDK
    1. Bitness :- x64
    2. v1.8
  2. JVM Arguments
    • -XX:+UseConcMarkSweepGC
      • Garbage Collection Algorithm
      • Web Links
    • HeapDumpPath = data
    • ErrorFile :- logs/hs_err_pid%p.log
Tab – Monitor
Image

es_Tab_Monitor_20180811_1005AM.PNG

Textual
  1. Heap
    • Max :- 256 MB
    • In use :-  120 MB

 

Tab – Threads
Image

es_Tab_Threads_20180811_1004AM

 

jconsole

Artifacts

JDK is accessible from the bin folder of Java’s JDK HOME folder.

Visual

Tab – Overview
Image

jconsole_Tab_Overview_20180811_1121AM

Textual
  1. Heap Memory Usage :- 137 MB
  2. Live Threads :- 60
  3. Classes :- 16,000
Tab – Memory
Image

jconsole_Tab_Memory_20180811_1112AM

Textual
  1. Used :- 156 MB
  2. Committed :- 256 MB

 

Tab – Threads
Image

jconsole_Tab_Threads_20180811_1128AM

Textual
  1. Live Threads :- 60
  2. Peak :- 63

 

 

References

  • Oracle
    • Home / Java / Oracle JDK 9 Documentation
      • Java Platform, Standard Edition Tools Reference
    • Java
      • jps
        • jps – Java Virtual Machine Process Status Tool
          Link
  • Karun Subramanian
    • 5 not so easy ways to monitor the Heap Usage of your Java Application
      Link
  • Dustin Marx
    • Dzone
      • jhsdb: A New Tool for JDK 9
        Link

Logstash – Error – ” Unrecognized VM option ‘UseParNewGC’ “

Background

During my initial evaluation of Logstash ran into an easy to address error.

Reproduce

Invoke

The invocation is straight forward


set "_binfolder=C:\Downloads\Elastic\Logstash\v6.3.2\extract\bin"
set "_configuration=stackOverflow2010.User.conf"

call %_binfolder%\logstashImpl.bat -f %_configuration%

Output

Image

UseParNewGC_20180802_0334PM

Textual


Unrecognized VM option 'UseParNewGC'
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

Troubleshooting

Java

Java Version

Outline

We can request the version Number for the java runtime executable, java.exe.

Script


java -version

Output

Image

java_version_20180808_0332PM

Textual


java version "10.0.2" 2018-07-17
Java(TM) SE Runtime Environment 18.3 (build 10.0.2+13)
Java HotSpot(TM) 64-Bit Server VM 18.3 (build 10.0.2+13, mixed mode)

Knowledge Base ( KB)

Googled on the the term and got a good hit

  1. [META] Java 10 Support #9345
    • Opened By :- andrewvc
    • Date Opened :- 2018-April-9th
    • Link
      Link
    • Resolution
      • Resolution #1
        • By :- Armin Braun ( original-brownbear )
        • Web Links
        • Workaround :- Currently there is no temporary fix sorry. Switching to Java 8 is the only stable solution for the time being.
      • Resolution #2
        • By :- Josh Daone ( JoshDaone )
        • Web Links
        • Workaround :- @original-brownbear thanks! Switched back to openjdk-8-jre and working properly.

Resolution

Target Specific Java Version

Objective

Rather than rely on Java that is referenced in our path, we will set the environment variable JAVA_HOME to a pre-v10 Version.

Review Installed Java

Launched Windows explorer and access the “C:\Program Files\Java” folder.

Review Java Folders

java_explorer_20180808_0424PM

Code


set "JAVA_HOME=C:\Program Files\Java\jdk1.8.0_181"

Additional Reading

  1. Andy Luis
    • mpvjava
      • JDK 9 Migration : 5 point checklist for Garbage Collection
        Link
      • About
        Link
  2.  OpenJDK
    • JEP 214: Remove GC Combinations Deprecated in JDK 8
      Link
  3. ORACLE
    • JDK 9 Release Notes – Removed APIs, Features, and Options
      Link

 

Dedicated

Dedicated to Armin Braun ( original-brownbear )
Less Talking … More Coding …

References

  1. Elastic
    • elastic/logstash
      [META] Java 10 Support #9345
      Link