Oracle tablespace – add space / resize / check autoextend / set autoextend off

-- checking tablespace sizes
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Tablespace|Used MB|Free MB|Total MB|Pct. Free|
----------|-------|-------|--------|---------|
SYSAUX | 606| 34| 640| 5|
UNDOTBS1 | 23| 2| 25| 8|
USERS | 2| 98| 100| 98|
SYSTEM | 352| 8| 360| 2|
--check auto extend for a datafile

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;
TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES |
---------------|---------------------------------------|--------------|-----------|
USERS |/u01/app/oracle/oradata/XE/users.dbf |YES |11811160064|
SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984|
UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984|
SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|
--set auto extend off for a datafile (I think you cannot change it back)

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf'
AUTOEXTEND OFF;

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;
TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES |
---------------|---------------------------------------|--------------|-----------|
USERS |/u01/app/oracle/oradata/XE/users.dbf |NO | 0|
SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984|
UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984|
SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|
--Resizing a datafile

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf'
RESIZE 100M;
--adding a new datafile for a tablespace (with Auto extend) to add more memory
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/XE/users3.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
--adding a new datafile for a tablespace (without Auto extend) to add more memory
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/XE/users2.dbf' SIZE 20M
AUTOEXTEND OFF;
Advertisement

Oracle SGA – System Global Area

The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas. Read more

Checking SGA size of your database:-

SELECT * FROM v$sgainfo;
NAME |BYTES |RESIZEABLE|
----------------------------------|----------|----------|
Fixed SGA Size | 2233344|No |
Redo Buffers | 5541888|No |
Buffer Cache Size | 444596224|Yes |
Shared Pool Size | 176160768|Yes |
Large Pool Size | 4194304|Yes |
Java Pool Size | 4194304|Yes |
Streams Pool Size | 0|Yes |
Shared IO Pool Size | 0|Yes |
Granule Size | 4194304|No |
Maximum SGA Size |1068937216|No |
Startup overhead in Shared Pool | 75497472|No |
Free SGA Memory Available | 432013312| |

As you see above, I have 1 GB of SGA in my local DB. Enterprise databases having around 1 TB of SGA is not unusual (to have more buffer cache to reduce the disk reads).

Also, you can see that I have around 424 MB buffer cache and 412 MB free SGA. Let me try to add the free SGA to Buffer cache.

SQL> show parameter cache_size;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size	     big integer 0
db_16k_cache_size		     big integer 0
db_2k_cache_size		     big integer 0
db_32k_cache_size		     big integer 0
db_4k_cache_size		     big integer 0
db_8k_cache_size		     big integer 0
db_cache_size			     big integer 0
db_flash_cache_size		     big integer 0
db_keep_cache_size		     big integer 0
db_recycle_cache_size		     big integer 0

SQL> alter system set db_cache_size = 410M scope=both;

System altered.

SQL> show parameter cache_size;


NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size	     big integer 0
db_16k_cache_size		     big integer 0
db_2k_cache_size		     big integer 0
db_32k_cache_size		     big integer 0
db_4k_cache_size		     big integer 0
db_8k_cache_size		     big integer 0
db_cache_size			     big integer 412M
db_flash_cache_size		     big integer 0
db_keep_cache_size		     big integer 0
db_recycle_cache_size		     big integer 0

Query to check SGA target advise:-

select * from v$sga_target_advice order by sga_size;

A DEAD Scrum Master is a USELESS Scrum Master – Ken Schwaber

In Ken Schwaber’s book, “Agile Project Management with Scrum“, he says

Lessons Learned
“The ScrumMaster’s job is to protect the team from impediments during the Sprint. However, the ScrumMaster has to operate within the culture of the organization. My mistake lay in
failing to recognize the value of teamwork to this organization. I had been a consultant for so long that I’d forgotten how much some large organizations cared about not rocking the
boat and keeping the corporate family together.
The ScrumMaster walks a fine line between the organization’s need to make changes as quickly as possible and its limited tolerance for change. Whenever possible, the
ScrumMaster makes a case and pushes the necessary changes through. The results are often greater productivity and greater return on investment (ROI). However, sometimes these
changes are culturally unacceptable and the ScrumMaster must acquiesce. Remember that Scrum is the art of the possible. A dead sheepdog is a useless sheepdog.“

Cobol Code analyser

I had to analyse / review COBOL code as part of my job, and I have always found it difficult to do it from the basic ISPF browser. My friend Selva has a created a useful, simple tool to make this easier.
You can download it here. It is a jar file that will work if you have Java Runtime Environment installed.

Let me know in the comments if you find it useful, or any suggestions for improvements.

(This is only an initial version of this tool. Selva is still working to introduce much more features).

t2

Click on the “Load” button to open any cobol file that you have downloaded from mainframe. The tree structure (Code flow) present in the right side, will list all the sections / paragraphs present in the code. You can double click any section in the tree to locate that part within the code. You can use ‘Ctrl + F’ shortcut to find any part code, and use F5 to reach next occurrences of the found word.

Download

Easytrieve – check if value IS NUMERIC in an alpha variable

Similar to how we have the ‘IS NUMERIC’ verb in COBOL, we can validate if a Alphanumeric variable contains Numeric value or not in Easytrieve.

Sample program:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 DEFINE WS-ALPHA W 6 A
 JOB INPUT NULL
* ----- NOT NUMERIC --------
 WS-ALPHA = 'AB1234'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
* ----- NUMERIC --------
 WS-ALPHA = '123456'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
* ----- NOT NUMERIC --------
 WS-ALPHA = '123.45'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
* ----- NOT NUMERIC --------
 WS-ALPHA = '  1234'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
 STOP

Sort card – replace selective fields (IFTHEN,WHEN)

Lets say, we want to replace the word ‘TEST’ in position 16 into ‘TEMP’

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-
123423452345   TEST  ARASDRGSDFGS
12342356563465 AAAA  JHFDGHDFSGDSF
7853425632456  TEST  JDFSGGDSFHJGGH
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=(16,4,CH,EQ,C'TEST'),OVERLAY=(16:C'TEMP'))

Lets say, we want to replace the word ‘TEST’ in position 16 into ‘TEMP’, and ‘TEMP’ in position 36 to ‘TEST’

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-
123423452345   TEST  ARASDRGSDFGS
12342356563465 AAAA  JHFDGHDFSGDSF TEMP  
7853425632456  TEST  JDFSGGDSFHJGGH
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=(16,4,CH,EQ,C'TEST'),OVERLAY=(16:C'TEMP')),
      IFTHEN=(WHEN=(36,4,CH,EQ,C'TEMP'),OVERLAY=(16:C'TEST'))

also, read about FINDREP, more about IFTHEN

ICETOOL to get maximum, minimum value records

ICETOOL SELECT FIRST / LAST to get largest / smallest values within a set

Suppose we have a file with Branch name, Account number, Transaction amounts. like,

Branch  Account-num  Transaction-Amount
----+----1----+----2----+----3----+----4----+
Chennai   10011     00523
Chennai   10011     00010
Bangalore 10011     00056
Bangalore 10011     00670
Chennai   10012     00200
Chennai   10012     00235
Chennai   10012     00750
Bangalore 10012     00750
Bangalore 10012     00034

and, we want to get the maximum value transactions for every account. like,

Branch  Account-num  Transaction-Amount
----+----1----+----2----+----3----+----4----+
Bangalore 10011     00670
Bangalore 10012     00750
Chennai   10011     00523
Chennai   10012     00750

this can be done using ICETOOL as,

//TOOLIN DD *
 SELECT FROM(INFILE) TO(OUTFILE) ON(BRANCH) ON(ACCOUNT) -
 FIRST USING(CTL1)
/*
//CTL1CNTL DD *
 SORT FIELDS=(BRANCH,A,ACCOUNT,A,TRANS-AMT,D)
/*
//SYMNAMES DD *
 BRANCH,1,10,CH
 ACCOUNT,11,5,ZD
 TRANS-AMT,21,5,ZD
/*

In the above ICETOOL step, we are sorting the file in the order of Branch name (Ascending), Account number (Ascending), Transaction amount (Descending).

(If you want to select the smallest/minimum value transactions only, then sort the Transaction amount in Ascending order).

Then for each Branch and Account, we are selecting the first record only.

what are SYMNAMEs?

ICETOOL to copy files

Direct copy of multiple files. Copy files from DD names INFILE1, INFILE2, INFILE3 to OUTFILE1, OUTFILE2, OUTFILE3

//TOOLIN DD *
 COPY FROM(INFILE1) TO(OUTFILE1)
 COPY FROM(INFILE2) TO(OUTFILE2)
 COPY FROM(INFILE3) TO(OUTFILE3)

Copy of multiple files with some processing. Copy files from DD names INFILE1, INFILE2, INFILE3 to OUTFILE1, OUTFILE2, OUTFILE3

//TOOLIN DD *
 COPY FROM(INFILE1) TO(OUTFILE1) USING(CTL1)
 COPY FROM(INFILE2) TO(OUTFILE2) USING(CTL2)
 COPY FROM(INFILE3) TO(OUTFILE3) USING(CTL3)
/*
//CTL1CNTL DD *
 SORT FIELDS=(1,5,CH,A)
/*
//CTL2CNTL DD *
 SORT FIELDS=(6,5,CH,A)
/*
//CTL3CNTL DD *
 SORT FIELDS=COPY
 OUTFIL CONVERT,
 OUTREC=(1:5,80,20X)

Easytrieve program to parse a string

Below program parses a string that contains 3 words which are separated by space.

FULL-NAME contains ‘KARTHIK KAR THIK’, which is split into First name, Middle name and Last name.

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 DEFINE IND1 W 3 N VALUE 1 
 DEFINE IND2 W 3 N VALUE 1 
 DEFINE FLAG W 1 N VALUE 1 
 DEFINE FULL-NAME W 30 A VALUE 'KARTHIK KAR THIK' 
 DEFINE FIRST-NAME W 15 A 
 DEFINE MID-NAME W 15 A 
 DEFINE LAST-NAME W 15 A 
 DEFINE FULL-NAME-ARRAY  FULL-NAME  1 A OCCURS 30 
 DEFINE FIRST-NAME-ARRAY FIRST-NAME 1 A.OCCURS 15 
 DEFINE MID-NAME-ARRAY   MID-NAME   1 A OCCURS 15 
 DEFINE LAST-NAME-ARRAY  LAST-NAME  1 A OCCURS 15 
 JOB INPUT NULL 
   DO WHILE IND1 LE 30 
      IF FULL-NAME-ARRAY(IND1) NE ' '
         CASE FLAG 
            WHEN 1 
              MOVE FULL-NAME-ARRAY(IND1) TO FIRST-NAME-ARRAY(IND2)
            WHEN 2 
              MOVE FULL-NAME-APRAY(IND1) TO MID-NAME-ARRAY(IND2) 
            WHEN 3 
              MOVE FULL-NAME-ARRAY(IND1) TO LAST-NAME-ARRAY(IND2) 
         END-CASE 
         IND2 = IND2 + 1 
      ELSE 
         IND2 = 1 
         FLAG = FLAG + 1
      END-IF 
      IND1 = IND1 + 1 
   END-DO 
   DISPLAY FIRST-NAME 
   DISPLAY MID-NAME 
   DISPLAY LAST-NAME 
STOP

Output will be,

KARTHIK
KAR
THIK

COBOL – PERFORM a SECTION

Below sample COBOL program explains how to PERFORM a SECTION.

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
       IDENTIFICATION DIVISION.       
       PROGRAM-ID.    KARTEST5.       
      * perform a SECTION             
       ENVIRONMENT DIVISION.          
       DATA DIVISION.                 
       PROCEDURE DIVISION.            
           PERFORM MY-SECTION.        
           GOBACK.
       MY-SECTION SECTION.            
           DISPLAY 'INSIDE MY-SECTION'
           EXIT.

Output of this program will be

INSIDE MY-SECTION