Most of the times we DBA's take on the developers like underdogs , even I used to but things have changed as my years of experience in the industry is growing by , Every person is equally important right from the application developers, DB developers, Network Administrator.... to DBA's . Anyone one role in the Software life Cycle is neglected , the whole application gets affected
I myself have neglected DB Development like creating procedures . Recently , very recently I happened to create some custom stored procedures which will help the DBA's , they save a lot of time
Ill discuss one case I have come across , I usually use the google drive to store my documents where in I store all my custom administrative queries I use daily
I use to copy paste the respective query depending on my need , because all the time you don't have sufficient time to write the query every time , say a tablespace DMS container size is full for some reason and you need to EXTEND it immediately , You cannot frame the query at that peak time which will be time consuming as every second counts
One day there came a issue and I don't have my laptop on hand , so connected through my other colleagues system where in I cannot access my google drive as I'm already connected to VPN , by the time I framed the query and issued the command some transactions in the application got affected
I then started thinking after resolving this issue as to how can I save this time I have wasted in framing my query at that peak time which made to go through basics of STORED PROCEDURES and yes they really help me a lot
Ill show you a sample case what I have dealt with , I use the below query every time to check my DMS tablespace containers free and used pages
SELECT A.TBSP_ID,SUBSTR(A.TBSP_NAME,1,20) AS TBSPNME,A.TBSP_TYPE ,A.TBSP_TOTAL_PAGES,A.TBSP_FREE_PAGES,SUBSTR(B.CONTAINER_NAME,1,70) AS CONTNME FROM SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B WHERE A.TBSP_NAME=B.TBSP_NAME AND A.TBSP_ID=B.TBSP_ID AND A.TBSP_TYPE='DMS' AND A.TBSP_AUTO_RESIZE_ENABLED=0
TBSP_ID TBSPNME TBSP_TYPE TBSP_TOTAL_PAGES TBSP_FREE_PAGES CONTNME
-------------------- -------------------- ---------- -------------------- -------------------- ----------------------------------------------------------------------
5 TRAN_INDEX DMS 3000 2928 /home/db2inst1/trans_index_cont
6 TRAN_DATA DMS 3000 2928 /home/db2inst1/trans_data_cont
2 record(s) selected.
I used to issue the ALTER TABLESPACE command for EXTENDING the pages
I created a simple procedure to accomplish this task like one below
CREATE PROCEDURE DB2INST1.SYSADM_TEST ( IN I_TYPE VARCHAR(3) ) Re : BEGIN ATOMIC DECLARE CUR CURSOR WITH RETURN TO CLIENT FOR SELECT A.TBSP_ID,SUBSTR(A.TBSP_NAME,1,20) AS TBSPNME,A.TBSP_TYPE ,A.TBSP_TOTAL_PAGES,A.TBSP_FREE_PAGES, SUBSTR(B.CONTAINER_NAME,1,70) AS CONTNME FROM SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B WHERE A.TBSP_ID=B.TBSP_ID AND A.TBSP_TYPE=I_TYPE AND A.TBSP_AUTO_RESIZE_ENABLED=0; DECLARE CUR1 CURSOR WITH RETURN TO CLIENT FOR SELECT A.TBSP_ID,SUBSTR(A.TBSP_NAME,1,20) AS TBSPNME,A.TBSP_TYPE ,A.TBSP_TOTAL_PAGES,A.TBSP_FREE_PAGES, SUBSTR(B.CONTAINER_NAME,1,70) AS CONTNME FROM SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B WHERE A.TBSP_ID=B.TBSP_ID AND A.TBSP_TYPE=I_TYPE; DECLARE CUR2 CURSOR WITH RETURN TO CLIENT FOR SELECT A.TBSP_ID,SUBSTR(A.TBSP_NAME,1,20) AS TBSPNME,A.TBSP_TYPE ,A.TBSP_TOTAL_PAGES,A.TBSP_FREE_PAGES, SUBSTR(B.CONTAINER_NAME,1,70) AS CONTNME FROM SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B WHERE A.TBSP_ID=B.TBSP_ID; IF (I_TYPE='DMS') THEN OPEN CUR; ELSEIF(I_TYPE='SMS') THEN OPEN CUR1; ELSE OPEN CUR2; END IF; END Re !
IF the I_TYPE is DMS it will execute the query below
SELECT A.TBSP_ID,SUBSTR(A.TBSP_NAME,1,20) AS TBSPNME,A.TBSP_TYPE ,A.TBSP_TOTAL_PAGES,A.TBSP_FREE_PAGES, SUBSTR(B.CONTAINER_NAME,1,70) AS CONTNME FROM SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B WHERE A.TBSP_ID=B.TBSP_ID AND A.TBSP_TYPE=I_TYPE AND A.TBSP_AUTO_RESIZE_ENABLED=0
IF the I_TYPE is SMS it will execute the query below
SELECT A.TBSP_ID,SUBSTR(A.TBSP_NAME,1,20) AS TBSPNME,A.TBSP_TYPE ,A.TBSP_TOTAL_PAGES,A.TBSP_FREE_PAGES, SUBSTR(B.CONTAINER_NAME,1,70) AS CONTNME FROM SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B WHERE A.TBSP_ID=B.TBSP_ID AND A.TBSP_TYPE=I_TYPE;
IF the I_TYPE is ALL it will execute the query below, of course I have not kept any validation here you can any three word combination
SELECT A.TBSP_ID,SUBSTR(A.TBSP_NAME,1,20) AS TBSPNME,A.TBSP_TYPE ,A.TBSP_TOTAL_PAGES,A.TBSP_FREE_PAGES, SUBSTR(B.CONTAINER_NAME,1,70) AS CONTNME FROM SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B WHERE A.TBSP_ID=B.TBSP_ID
So when I call the procedures with input as SMS it returns output as show below
call DB2INST1.SYSADM_TEST('SMS')
Result set 1
--------------
TBSP_ID TBSPNME TBSP_TYPE TBSP_TOTAL_PAGES TBSP_FREE_PAGES CONTNME
-------------------- -------------------- ---------- -------------------- -------------------- ----------------------------------------------------------------------
1 TEMPSPACE1 SMS 1 0 /home/db2inst1/NODE0000/SAMPLE/T0000001/C0000000.TMP
4 SYSTOOLSTMPSPACE SMS 1 0 /home/db2inst1/NODE0000/SAMPLE/T0000004/C0000000.UTM
2 record(s) selected.
Return Status = 0
For DMS
call DB2INST1.SYSADM_TEST('DMS')
Result set 1
--------------
TBSP_ID TBSPNME TBSP_TYPE TBSP_TOTAL_PAGES TBSP_FREE_PAGES CONTNME
-------------------- -------------------- ---------- -------------------- -------------------- ----------------------------------------------------------------------
5 TRANSACTION_INDEX DMS 3000 2928 /home/db2inst1/trans_index_cont
6 TRANS_DATA DMS 3000 2928 /home/db2inst1/trans_data_cont
2 record(s) selected.
Return Status = 0
And when the input is ALL
call DB2INST1.SYSADM_TEST('ALL')
Result set 1
--------------
TBSP_ID TBSPNME TBSP_TYPE TBSP_TOTAL_PAGES TBSP_FREE_PAGES CONTNME
-------------------- -------------------- ---------- -------------------- -------------------- ----------------------------------------------------------------------
0 SYSCATSPACE DMS 66452 5768 /home/db2inst1/NODE0000/SAMPLE/T0000000/C0000000.CAT
1 TEMPSPACE1 SMS 1 0 /home/db2inst1/NODE0000/SAMPLE/T0000001/C0000000.TMP
2 USERSPACE1 DMS 57344 8448 /home/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG
3 SYSTOOLSPACE DMS 8192 5464 /home/db2inst1/NODE0000/SAMPLE/T0000003/C0000000.LRG
4 SYSTOOLSTMPSPACE SMS 1 0 /home/db2inst1/NODE0000/SAMPLE/T0000004/C0000000.UTM
5 TRANSACTION_INDEX DMS 3000 2928 /home/db2inst1/trans_index_cont
6 TRANS_DATA DMS 3000 2928 /home/db2inst1/trans_data_cont
7 record(s) selected.
Return Status = 0
So I have created procedures of my Administrative SQL's , most important one's which I regularly use. So next time I need not worry about the script documentation . They are right in my SCHEMA.
So go speak with your developers broaden your knowledge
Have a Nice Day
Now i should also learn writing some basic procs for daily use.
ReplyDeleteYa actually it really comes to use in our daily life like the table functions @Murali Chava
ReplyDelete