DB2 - with (nolock) equivalent for IBM DB2

Please subscribe on my YouTube Channel.


MS SQL Server 2008 :

Select * FROM MyTable as A with (nolock)


DB2: Uncomitted Read = WITH UR

SELECT * FROM myTable WITH UR

DB2 - How to find Host Name of server

Below worked for me.

SELECT * FROM TABLE(SYSPROC.ENV_GET_SYS_INFO());

DB2 - How to determine the DB2 LUW Product, Version and Fix Pack level of an Instance

Both worked for me.

SELECT * FROM TABLE(SYSPROC.ENV_GET_INST_INFO());

or

SELECT * FROM SYSIBMADM.ENV_INST_INFO;

Oracle - How to check Database Version

This will give you the detailed version number of the database components.

SELECT * FROM V$VERSION;

or You could run

Select * from PRODUCT_COMPONENT_VERSION;


Referenced from

https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba004.htm#ADMIN11032

DB2 - Isnull function

Use COALESCE 

The COALESCE function returns the value of the first nonnull expression.

eg


select COALESCE( null,'noNullIsselected') as Col from sysibm.sysdummy1

DB2 - DUAL Table DB2: sysibm.sysdummy1

select current date from sysibm.sysdummy1

CTE - Database File system full

CTE makes the code more readable but it uses the file system to store the temporary results and thus may cause "SQL0968C file system is full. SQLSTATE=57011" error.
Instead, create a temporary table/s to store interim results.

DB2 - Create a Copy of Table

Below will create an empty table

create table MPI_MY_TABLE_bkp as (select * from MPI_MY_TABLE ) WITH NO DATA ;

or

db2 "create table analysis_LoadData as (select memidnum as tmemrecno,memidnum as tmemidnum,memidnum as tbsilo from mpi_memhead fetch first 1 rows only) with no data"



with Data doesn't work in DB2 version 10.5


INSERT INTO MPI_MY_TABLE_bkp (SELECT * FROM MPI_MY_TABLE);

or


db2 "CREATE TABLE "ramitTempMemrecnoEIDMemIDNum" ( MEMRECNO BIGINT, EID BIGINT, MEMIDNUM VARCHAR(240) )
"

DB2 - Import csv file into db2 table - Load Data From File

Truncate the Table.

db2 "truncate analysis_LoadData IMMEDIATE" Connect to Database db2 connect to MDM user db2inst using password awk '{print $1","$1","$1}' UpdateList.txt > list2BeLoaded.txt

Input File - list2BeLoaded.txt 

Target Table - analysis_LoadData db2 import from list2BeLoaded.txt of del insert into analysis_LoadData or db2 load ---which is faster then db2 import

DB2 - Transaction log for the database is full

If you receive this error means you are running a query which is returning to many rows which can't be kept in the buffer. The workaround is to do a commit or rollback to clear the log and try to frame a query with a smaller set of data.

DB2 - List all the tables

list tables or db2 "select tabname,owner from syscat.tables" or db2 "select name,creator from sysibm.systables where name like '%anyStringInTableName%'"

DB2 - run query from file

Make Connection to Database

db2 connect to MDMDB user db2inst1 using password

db2 -tvf query.sql