SQL test query or validation query

 

Ref: https://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most

Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0 has a property called preferredTestQuery, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has validationQuery.

-- Access

SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 

-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica

SELECT 1

-- MemSQL, Oracle

SELECT 1 FROM DUAL

-- CUBRID

SELECT 1 FROM db_root

-- Db2

SELECT 1 FROM SYSIBM.DUAL

-- Derby

SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird

SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere

SELECT 1 FROM SYS.DUMMY

-- HSQLDB

SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix

SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata

SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

Import DUMP file in Oracle

1. Create user and grant permission
   alter session set "_ORACLE_SCRIPT"=true;
   create user [username] identified by [password];
   grant connect, create session, imp_full_database to [username];
   CREATE SMALLFILE TABLESPACE [TABLESPACE_NAME] DATAFILE 'FILEDATA.dbf' SIZE 7G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
   GRANT UNLIMITED TABLESPACE TO  [username];
2. Create Directory
   CREATE DIRECTORY BACKUP_DIR AS '/home/oracle/import';
   GRANT read,write on DIRECTORY BACKUP_DIR to   [username];
3. Import
    impdp [username]/[password] DIRECTORY=BACKUP_DIR  DUMPFILE=File.dmp FULL=Y LOGFILE=import.log

IBM Integration Bus- Database Definition

Securing database connections

mqsisetdbparms broker_name -n jdbc::security_identity -u userID -p password

Download JDBC driver for type 4 connections

Example for Oracle: https://www.oracle.com/database/technologies/jdbc-drivers-12c-downloads.html 

Setting up a JDBC provider for type 4 connections

mqsicreateconfigurableservice LOCALBROKER -c JDBCProviders -o JDBCOracleDBConnector -n connectionUrlFormat,connectionUrlFormatAttr1,connectionUrlFormatAttr2,connectionUrlFormatAttr3,connectionUrlFormatAttr4,connectionUrlFormatAttr5,databaseName,databaseType,databaseVersion,description,environmentParms,jarsURL,jdbcProviderXASupport,maxConnectionPoolSize,portNumber,securityIdentity,serverName,type4DatasourceClassName,type4DriverClassName -v jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1],MYDB,,,,,MYDB,Oracle,11.2,"Oracle DEV",default_none,"C:\temp\jars",false,0,5001,security_identity,dev72.hdd.com,oracle.jdbc.xa.client.OracleXADataSource,oracle.jdbc.OracleDriver

Please note: Please correct  the below information 

- securityIdentity( ex: security_identity is same with step Securing database connections)

- jarsURL(ex: "C:\temp\jars" is folder contains the jdbc driver jar files).

- connectionUrlFormat( ex: "jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1]"  is jdbc url  to connect to  the BD).

-  databaseName( Ex: "MYDB").

- serverName( Ex: "dev72.hdd.com")

- type4DatasourceClassName( Ex: com,oracle.jdbc.xa.client.OracleXADataSource)

- type4DriverClassName( Ex: oracle.jdbc.OracleDriver).

- Provider name( Ex: JDBCOracleDBConnector).

Create Database Definition

Create database definition( note that  provider name should be same with jdbc provider name as ablove, ex: JDBCOracleDBConnector )