DB2 related collection

Create Table

create table TABNAME (ORDERS_ID BIGINT, BRAND  VARCHAR(10) , MIGRATIONRUN_ID  BIGINT, STARTTIME TIMESTAMP, MIGRATIONSTATUS CHAR(2),

constraint constraint f_orders foreign key (ORDERS_ID) references ORDERS (ORDERS_ID));

Creating Index on table

CREATE INDEX ITABNAME ON TABNAME (BRAND , MIGRATIONSTATUS);

Creating unique index:

db2 create unique index <index_name> on

<table_name>(<unique_column>) include (<column_names..>)

Dropping indexes

db2 drop unique index <index_name> on

<table_name>(<unique_column>) include (<column_names..>)

Getting indexes of perticular table :

db2 "select * from syscat.indexes where tabname   = 'your table name here' \

                                  and   tabschema = 'your schema name here'"

OR

SELECT INDNAME, DEFINER, TABSCHEMA, TABNAME,

COLNAMES, COLCOUNT, UNIQUERULE, INDEXTYPE

FROM SYSCAT.INDEXES

WHERE TABSCHEMA NOT LIKE 'SYS%' AND

TABNAME = 'TABLE_NAME'


create table TABNAME  (ORDERS_ID BIGINT NOT NULL, BRAND  VARCHAR(10) , MIGRATIONRUN_ID  BIGINT NOT NULL, STARTTIME TIMESTAMP, MIGRATIONSTATUS CHAR(2),
constraint p_xordmig  PRIMARY KEY (ORDERS_ID, MIGRATIONRUN_ID),constraint f_orders foreign key (ORDERS_ID) references ORDERS (ORDERS_ID));
Alter Table 

Change to make not null column
ALTER TABLE TABNAME  ALTER COLUMN <ORDERS_ID> SET NOT NULL;

ALTER TABLE TABNAME  ALTER COLUMN MIGRATIONRUN_ID SET NOT NULL;

Change to make primary key
ALTER TABLE XORDERSMIGRATION_OR02 ADD CONSTRAINT  p_xordmig  PRIMARY KEY (ORDERS_ID, MIGRATIONRUN_ID);

Dropping a table

DROP TABLE TABNAME


Duplicate entry in column

select count(*), CATENTRY_ID, ATTRIBUTE_NAME from XATTRNONECMC

group by CATENTRY_ID, ATTRIBUTE_NAME

Count column will give number of entry