parameter cannot be modified because specified value is invalid

 ALTER SYSTEM SET db_4k_cache_size=128M

Error report -

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache

02097. 00000 -  "parameter cannot be modified because specified value is invalid"

*Cause:    Though the initialization parameter is modifiable, the modified

           value is not acceptable to the parameter.

*Action:   Check the DBA guide for range of acceptable values for this

           parameter.

Solution is to set 

show parameter sga_target;

alter system set sga_max_size=2G scope=spfile;

alter system set sga_target=1024M scope=spfile;

and the restart the Database server and try to run the command again it worked for me.

ALTER SYSTEM SET db_4k_cache_size=128M

System SET altered.

Join Order --- Left Join with Inner Join - Control Join Precedence Order

In SQL all join conditions have the same precedence so 

like in mat 1 -2 + 3   = (1 - 2) +3 and not 1 - (2 + 3)

similarly in SQL T1 Left Join T2 Inner Join T3 is evaluated as (T1 Left Join T2) Inner Join T3  

If the T3 Join condition doesn't return anything, it will make the output of  T1 and T2 also not show up in output. 

 Select * from  T1

Left Join (T2 left join T3 on T2.id = T3.ID ) 

on T2.id =T1.id

Oracle : How to kill a session.

Select * from v$sessions.

or

select 'Alter System kill session ' || '''' || sid || ',' || serial# || ''':' || from v$sesion where username='NameOfUserWhoseSessionNeedsToBeKilled'

DB2 - Catalog to remote server

Catalog the server node and the database.

a. In the command window, run the following command:

db2 catalog tcpip node name remote DB Server {<host name> or <IP Address server Port number used>}

b. In the command window, run the following command:

db2 catalog db database name at node name authentication SERVER

DB2 - VIP instance list and Primary Node

Run the following command to get the Database name.






db2 get db cfg for DB2|grep HADR

output would something like

 HADR database role                                      = PRIMARY
 HADR local host name                  (HADR_LOCAL_HOST) = NameOfPrimaryNode
 HADR local service name                (HADR_LOCAL_SVC) = hadr01
 HADR remote host name                (HADR_REMOTE_HOST) = NameOfSecondyNode
 HADR remote service name              (HADR_REMOTE_SVC) = hadr01

DB2 - List the alias Name

Run the following command  to get the alias Name:

 db2 list database directory


Output

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = DB2ALIAS
 Database name                        = DB2
 Node name                            = DB2NODE
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

References -

https://www.ibm.com/developerworks/data/library/techarticle/dm-1409hadr-db2-tivoli/index.html

Oracle - Date- Truncate - EXTRACT - To_Char

Truncate a date.


The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day. Please refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.



Below will truncate timestamp part from a dateTimeStamp Field

select trunc(dateTimeStamp),count(*) from myTable

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92 

EXTRACT (datetime)

extract_datetime::=

e.g

select extract( month from sysdate) from dual

select extract( year from sysdate) from dual

TO_CHAR

select to_char( sysdate, 'mm' ) from dual ; -- returns 01..12
select to_char( sysdate, 'MON' ) from dual ;-- returns JAN, FEB, ... DEC 
select to_char( sysdate, 'Month' ) from dual ;-- returns January, ... December

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