PostgreSQL :- Function/Metadata

Background

Now that we have a couple of functions written, let us see how to review metadata on functions.

Code

SQL


select 

         tblNS.nspname

       , tblP.proname

       , tblLang.lanname
            as "language"

        , tblAuth.rolname
            as "owner"

        , tblP.prokind

        , tblType.typname
            as "returnType"

        , tblP.proparallel

        , tblP.provolatile

       , tblP.oid::regprocedure
            as "syntax"

        , tblP.proargnames

        , tblP.prosrc

from pg_catalog.pg_proc tblP

inner join pg_catalog.pg_namespace tblNS

    on tblP.pronamespace = tblNS.oid

join pg_catalog.pg_authid tblAuth

    on tblP.proowner = tblAuth.oid

join pg_catalog.pg_language tblLang

    on tblP.prolang = tblLang.oid

join pg_catalog.pg_type tblType

    on tblP.prorettype = tblType.oid

where tblNS.nspname not in

        (
              'information_schema'
            , 'pg_catalog'
        )

Output

pg_catalog.pg_proc.20190813.0651AM

GUI

DBeaver

Outline

To review function properties, please do the following :-

  1. Launch DBeaver
  2. Connect to PostgreSQL Instance
  3. Navigate to Schema \ {Schema} \ Functions \ {Function}
    • Function Parameters
    • Navigate on Selection
    • From the drop down menu, please choose “Function Parameters”

Image

Image – Function Parameters

func_parameters_20190813_0655AM.PNG

Image – Function Dependencies

func_dependency_20190813_0655AM.PNG

Image – Function Properties/Statistics/Flags

func_properties_20190813_0657AM.PNG

Image – Function – Permissions

func_permissions_20190813_0658AM.PNG

Image – Function – Source

func_source_20190813_0659AM.PNG

 

DBeaver / ProgreSQL – Array Datatype

Background

In our last post spoke of a little issue we experienced issuing a query against a PostgreSQL Instance.

Issue

The Issue is that our result set included an array column.

array_agg

Documentation

Link

array_agg.doc.png

SQL

select 

          tblUSR.name
            as "region"

        , array_agg
            (
                tblUSS.name
            )
                as "stateAsArray"

from   us.state tblUSS

inner join us.region tblUSR

        on tblUSS.region = tblUSR.name

group by
    tblUSR.name

order by
    tblUSR.name

Output

state.region.array.01.20190809.0915AM.PNG

Troubleshooting

Dbeaver

Support

Visited dbeaver support website @ Github.

Here is the URL ( https://github.com/dbeaver/dbeaver/issues ) [ Link ]

Issued a query matching on PostgreSQL ( Link).

Matched

Here are the matches :-

Image

issue.array.01.20190809.0407PM.PNG

Textual

  1. Web Link :- https://github.com/dbeaver/dbeaver/issues/6437 ( Link )
  2. Opened
    • Opened By :- pyrocks
    • Opened On :- Commented 5 days ago ( 2019-08-04 )
  3. Fixed
    • Confirm fixed in 6.1.4

 

Remediation

Check your current version.

If prior to v6.1.4, please download and apply new version.

Here is what our version number looked like prior to upgrade.

issue.v6.1.3.PNG

 

PostgreSQL / Explain Plan :- DBeaver & Azure Data Studio

Background

Playing around some more with PostgreSQL.

SQL

Here is the simple SQL.


select *

from   public.order_details tblOD

left outer join public.orders tblO

		on tblOD.order_id = tblO.order_id

where  tblOD.product_id = 10

Dbeaver

SQL Editor

Enter SQL in to DBeaver SQL Editor.

Execution Plan

Request

Request via Menu – Explain Execution Plan

Initiate Request

To get the query’s execution plan, please do the following :-

  1. Select the Query
  2. Execute the Query
  3. The Query is executed and it’s result is shown in a new window
  4. Show Execution Plan
    • Click on the menu item and choose “SQL Editor” \ “Explain Execution Plan”
Result
Image

northwind.orderDetails.01.20190908.0825AM.PNG

Issues

  1. The Output grid is “Simple”
    • Unfortunately, it does not appear easy nor intuitive to select all the cells, capture the selection, and copy it

 

Request via Query Prefixed with EXPLAIN

Initiate Request

To get the query’s execution plan, please do the following :-

  1. Enter the Query
  2. Prefix the entered with PostgreSQL keyword “EXPLAIN
  3. Execute the Query
  4. The Query is executed and the query’s execution plan is returned
Result
Image

sql.prefixedWithExplain.01.20190808.0836AM.PNG

Issues

  1. None
    • The query is display in a normal output window
      • There are two visible panes, Grid and Text

 

Azure Data Studio

SQL Editor

Enter SQL in to Azure Data Studio SQL Editor.

Execution Plan

Request

Request via Explain button

Initiate Request

To get the query’s execution plan, please do the following :-

  1. Select the Query
  2. Please click the Explain button
  3. The query’s Execution Plan is returned
Result
Image

azureDataStudio.button.explain.01.20190808.0841AM

Explain
  1. It is easy and intuitive to select and copy all the “Execution Plan” steps

Summary

Both DBeaver and Azure Data Studio offers good standard support for requesting and viewing a query’s execution plan.

Unfortunately the output generated via DBeaver “explain plan” is a generic textual dump.

It likely makes sense as it is likely intended to support all databases and so it is a simple canvas.

That shortcoming is easily eclipsed by simply requesting the Execution Plan via PostgreSQL “EXPLAIN” keyword.

DBeaver / SQL Anywhere – Database Connection Setup

Background

Wanted to quickly add SQL Anywhere to the list of databases that DBeaver can connect to.

Outline

  1. SAP SQL Anywhere Database Client
    • Artifacts
      • Download
    • Install
  2. Dbeaver
    • Register Data Source
    • Configure Data Source
    • Connect

 

Tasks

SAP SQL Anywhere Database Client

Artifacts

Anywhere Database Client is available :-

SAP SQL Anywhere Database Client Download
Home > Community > Archived Documents > SAP SQL Anywhere

Link

Install

Please install.

DBeaver

Launch

Please launch DBeaver

Database Connection

Unfortunately, SQL Anywhere is not one of the Databases availed by default within DBeaver.

Let us to go register it as Data Source.

Outline

  1. Access Driver Manager
    • Click on menu option “Database” \ “New Driver Connection”
  2. Driver Manager Window
    • Click on the New Button
  3. Configure Driver Manager
    • Driver Name
      • Anything you want it to be
      • For use, we used “SQL Anywhere”
    • Driver Type
      • Generic
    • Class Name
      • sap.jdbc4.sqlanywhere.IDriver
    • Windows Authentication ( Yes or No )
      • Yes, Windows Integration
        • URL Template
          • jdbc:sqlanywhere:host={host}:{port};database={database}
        • No authentication
          • We checked the “No authentication” check box
      • No, Username  & password Credential
        • URL Template
          • jdbc:sqlanywhere:host={host}:{port};database={database};UserID={username};Password={password}
        • No authentication
          • We ensured that “No authentication” is not checked
          • This forces us to use username/password credential
    • Libraries
      • {SQL Anywhere } \ Java\ sajdbc4.jar
      • {SQL Anywhere } \ Bin64\ dbjdbc17.jar

 

Images

New/Edit Driver

DriverManager.SQLAnywhere.01.20190724.1205PM

 

Connection

Unfortunately, SQL Anywhere is not one of the Databases availed by default within DBeaver.

Let us to go register it as Data Source.

Outline

  1. Create New Connection
    • JDBC URL
      • jdbc:sqlanywhere:host={host}:{port};database={database}
    • Host
      • Enter machine name
    • Port
      • Enter port number
    • Database/Schema
      • Enter database name

 

Images

New/Edit Connection

SQLAnywhere.connection.01.20190724.1214PM.PNG

 

References

  1. SAP
    • Home > Community > Archived Documents > SAP SQL Anywhere
    • SAP IQ Programming Reference
      • Home/JDBC support/SQL Anywhere JDBC driver/
        • SQL Anywhere JDBC driver connection strings
          Link
    • SQL Anywhere 17
      • SQL Anywhere 17 » SQL Anywhere Server – Programming » JDBC support » SQL Anywhere JDBC driver
        • SQL Anywhere JDBC driver connection strings
          Link
    • SAP SQL Anywhere Forum
      • JDBC driver for Oracle Data Modeler
        Link
  2. Apache.org
    • ambari.apache.org
      • ambari git commit: AMBARI-16157. JDBC Connect String for Ranger modified wrong for non-default port.(vbrodetskyi)
        Link

DBeaver – Portable

Background

Needing to take a quick look at an embedded database and I did not have a client installed on the server and I did not have firewall rules in place to engage from my desktop.

DBeaver

DBeaver is my universal database client.

Installation

I did not feel like going through a normal install.

Portable

Sometimes I just want a lithe footprint use experience.

Artifacts

Googled for Dbeaver portable and arrived here :-

Here
here

Image

The current portable version is 6.1.2-12 and our targeted OS is MS Windows, 64-bits.

v6.1.2-12.platform.windows.x64.firefox.01.20190713.0940PM.PNG

 

Install

Ran the installer

Outline

  1. Window – Welcome
    • The initial screen does not have an actual name
    • In most installers, it is called the Welcome Screen
  2. Window – Notice of Non-Affiliation and Disclaimer
    • Acknowledges that Portapps is not associated with DBeaver
  3. Window – License Agreement
  4. Window – Select Destination Location
    • By default the destination location is the system drive (C:)
    • Please choose one of application drives ( D:, E:, Etc)
  5. Window – Ready to Install
  6. Window – Installing
  7. Window – Completing

Images

Image – Welcome

setup.01.20190713.0943PM.PNG

Image – Notice of Non-Affiliation and Disclaimer

noticeOfNonAffliation.02.20190713.0944PM.PNG

Image – License Agreement

licenseAgreement.03.20190713.0945PM.PNG

Image – Select Destination Location
Original

selectDestinationLocation.04.01.20190713.0945PM.PNG

Revised

selectDestinationLocation.04.02.20190713.0945PM.PNG

Image – Ready to Install

readyToInstall.05.20190713.0946PM.PNG

Installing

installing.06.20190713.0947PM.PNG

Completing

completing.07.01.20190713.0947PM.PNG

Use

To use, please navigate to the folder you selected during install.

Database Platform

MySQL
Outline
  1. Please choose the database platform you will be targeting
  2. DBeaver prompts you as to whether it is OK to download the Driver
    • Acquiescence
  3. Configure Data Source
    • Database Host Name
    • Database Host Port Number
    • Username
    • Password

 

Our targeted database platform is MySQL, chose that platform.

Driver
Driver – Download
mysql.driver.download.01.20190713.0950PM.PNG
Driver – Test

mysql.driver.connect.01.20190713.0951PM.PNG

Dedicated

Dedicated to Portapps, Inc. @ portapps.io

And, Dbeaver ( Serge Rider and the entire mean gang ); you guys do good work.  Actually great work.

DBeaver – Generating SQL from existing data

Background

A good man reached out to me and prepped me about an upcoming Change Request.

The Change will be to add a lone record.

Options

Here are a couple of options:

  1. Have him add it to Development / QA
    • I will use Linked Server to bring it over to Production
    • Create corresponding SQL Statements and apply generated SQL

 

Generated SQL

Dbeaver

Outline

  1. Generate Data
    • Object
    • SQL Query
  2. Review Data Pane
  3. Select Data Rows
  4. Using menu, access “Generate SQL”\”Insert”

 

Generate Data

Depending on your needs, choose to generate data against the selected table or view. Or write a custom SQL.

Object

If it will expedite your needs, please choose a table, view, function, or Stored Procedure.

When table or view, please right click on the selected object and choose “Edit data”.

SQL Query

Enter SQL Query

SQL Query – Text

select *
from WideWorldImportersDW.Dimension.City

SQL Query – Image

paneQuery.20181102.0758AM

Execute Query.

Review Data Pane

Here is Generated data

paneData.20181102.0757AM.PNG

Data Pane – Select Data Rows

In the Data Pane, please select the data rows.

 

Data Pane, Generate SQL/Insert

Right click on the selected records and choose to generate SQL/Insert.

Generated SQL

WideWorldImportersDW.Dimension.City

paneData.Insert.Dimension.City.20181102.0807AM

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