Data Filtering current_date -7 days

I run query REFRESH TABLE DEV.MQT_AAA_APN_D_H7 on the sql tools such as toad is running well but i get errors when refresh table on db2 console.

This query MQ tables (MQT_AAA_APN_D_H7):
select DAY_ID, PRODUCT_ID, HIT, sum(sendkbytes) as SENDKBYTES, sum(receivekbytes) as RECEIVEKBYTES
from DEV.aaa_apn_d_t
WHERE day_id > cast(replace(char(current_date - 7 days),'-','') as integer) group by DAY_ID,PRODUCT_ID;

Continue reading


Limit the number of rows in DB

How can I limit the number of rows in DB2, MySQL, Oracle, SQL Server

in Oracle:
SELECT * FROM Table_Name where rownum <= 5

in DB2:
SELECT * FROM Table_Name fetch first 5 rows only

in MySQL:
SELECT * FROM Table_Name limit 5

in SQL Server:
SELECT TOP 5 * from Table_Name

hopefully useful… šŸ™‚


Create Catalog to connect DB2 server

The following command is how to Create Catalog in DB2

C:\Program Files\IBM\SQLLIB\BIN>db2 catalog tcpip node DEV_NODE remote 10.22.199.22 server 50000

C:\Program Files\IBM\SQLLIB\BIN>db2 catalog database DEV_DB as DEV_DB at node DEV_NODE authentication server

Continue reading


How to check DB2 Status

pada level OS bisa dicek dengan command:
$ ps -ef |grep db2

pada DB2 prompt:
$ db2 connect to bcudb user bcuaix using passwd
Database Connection Information
Database server = DB2/AIX64 9.5.1
SQL authorization ID = BCUAIX
Local database alias = BCUDB

Continue reading


How to create a self-signed SSL Certificate

The following command steps I did to generateĀ  SSL Certificates on AIX Version 5.3!

Step 1: Generate a Private Key
openssl genrsa -des3 -out server.key 1024

Step 2: Generate a CSR (Certificate Signing Request)
openssl req -new -key server.key -out server.csr

Continue reading


SQL0668N Operation not allowed for reason code “x” on table

When you loading all database with db2move command, propably most of the tables being loaded are in pending state. To check which ones, execute SQL statement:

SELECT tabname,status,const_checked FROM syscat.tables WHERE status=’C’
This command will list all tables in pending state

Reson code:

* “1” – table is in pending state – to fix this execute
SET INTEGRITY FOR table_name IMMEDIATE CHECKED

* “3” – previous load operation on this table failed. You have to terminate or restart previous load command

source: http://www.dbforums.com/6464822-post1.html


DB2 Tablespace Backup Pending Status

Berikut DB2 CommandĀ  untuk me-list tablesapce in pending status:

SELECT SNAPSHOT_TIMESTAMP,TBSP_NAME,DBPARTITIONNUM,TBSP_I D,TBSP_STATE,TBSP_PREFETCH_SIZE,TBSP_NUM_QUIESCERS ,TBSP_STATE_CHANGE_OBJECT_ID,TBSP_STATE_CHANGE_TBS P_ID,TBSP_MIN_RECOVERY_TIME,TBSP_TOTAL_PAGES,TBSP_ USABLE_PAGES,TBSP_USED_PAGES,TBSP_FREE_PAGES,TBSP_ PENDING_FREE_PAGES,TBSP_PAGE_TOP,REBALANCER_MODE,R EBALANCER_EXTENTS_REMAINING,REBALANCER_EXTENTS_PRO CESSED,REBALANCER_PRIORITY,REBALANCER_START_TIME,R EBALANCER_RESTART_TIME,REBALANCER_LAST_EXTENT_MOVE D,TBSP_NUM_RANGES,TBSP_NUM_CONTAINERS,TBSP_INITIAL _SIZE,TBSP_CURRENT_SIZE,TBSP_MAX_SIZE,TBSP_INCREAS E_SIZE,TBSP_INCREASE_SIZE_PERCENT,TBSP_LAST_RESIZE _TIME,TBSP_LAST_RESIZE_FAILED
FROM "SYSIBMADM"."SNAPTBSP_PART"
where TBSP_STATE<>'NORMAL' order by TBSP_NAME, DBPARTITIONNUM

Untuk memperbaikiĀ  Tablespace Backup Pending Status agar kembali Normal, jalankan query berikut:
db2 "backup db bcudb on dbpartitionnums(0,1,2,3,4,5,6) tablespace (USERSPACE1) online to /dev/null without prompting"

source: http://www.dbforums.com/6464821-post5.html