0

I have written this code using the DBI module , Here is the code

#!/usr/local/bin/perl -w
  use DBI;
  use strict;
# Open a connection
  my $dbh = DBI->connect("dbi:DB2:awdrt", "db2inst1", "db2inst1", {RaiseError => 1});
# use VALUES to retrieve value from special register
  my $stmt = "select RTRIM(substr(A.TBSP_NAME,1,30)),A.TBSP_TYPE as TYPE,A.TBSP_FREE_PAGES as FREE,B.CONTAINER_NAME as CON_PATH from SYSIBMADM.TBSP_UTILIZATION A ,SYSIBMADM.CONTAINER_UTILIZATION B where A.TBSP_ID=B.TBSP_ID and A.TBSP_AUTO_RESIZE_ENABLED=0 with UR";
  my $sth = $dbh->prepare($stmt);
  $sth->execute();
# associate variables with output columns...
  my ($col1,$col,$col3,$col4);
  $sth->bind_col(1,\$col1);
  $sth->bind_col(3,\$col3);
  $sth->bind_col(4,\$col4);
  while ($sth->fetch) { if ($col3 <= 2000){
  print "$col1 has $col3 pages with container $col4\n";}
        }
  $sth->finish();
  $dbh->disconnect();

Here's the O/p : 

TRANS_DATA has 1616 pages with container /adrst/bdts/trans_data_container
MASTER_INDEX has 1872 pages with container /adrst/bdts/master_index_container
TRANSACTION_INDEX has 1856 pages with container /adrst/bdts/transaction_index_container

And the table data when the query is executed is :

1                              TYPE       FREE                 CON_PATH                                                                                                                                                                                   
------------------------------ ---------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERSPACE1                     DMS                       15056 /adrst/bdts/userspc_container                                                                                                                                                              
USERSPACE1                     DMS                       15056 /adrst/bdts/userspc_container1                                                                                                                                                             
MASTER                         DMS                        3584 /adrst/bdts/master_container                                                                                                                                                               
TRANS_DATA                     DMS                        1616 /adrst/bdts/trans_data_container                                                                                                                                                           
MASTER_INDEX                   DMS                        1872 /adrst/bdts/master_index_container                                                                                                                                                         
TRANSACTION_INDEX              DMS                        1856 /adrst/bdts/transaction_index_container                                                                                                                                                    
TEMP_SYS                       DMS                        2192 /adrst/bdts/temp_sys_container                                                                                                                                                             
AUDIT_DATA                     DMS                        3360 /adrst/bdts/audit_data_container                                                                                                                                                           
TEMP_USR                       DMS                        2672 /adrst/bdts/temp_usr_container                                                                                                                                                             
TSASNCA                        DMS                        2840 /home/db2inst1/db2inst1/NODE0000/SQL00002/TSASNCA                                                                                                                                          
TSASNUOW                       DMS                        2880 /home/db2inst1/db2inst1/NODE0000/SQL00002/TSASNUOW                                                                                                                                         
TSASNAA                        DMS                        3712 /home/db2inst1/db2inst1/NODE0000/SQL00002/TSASNAA                                                                                                                                          
TSCDADDRESSMASTER              DMS                        2048 /home/db2inst1/db2inst1/NODE0000/SQL00002/CDADDRESSMASTER                                                                                                                                  

  13 record(s) selected.

What i am trying here basically report tablespaces with less than 2000 pages , Now my question is how do i do it without this module , What would be the better options , Hash,regex,grep? if so i need three columns to be reported as o/p , How do i do that using these or any thing that may come use?

Help is appreciated.....

mviswa
  • 147
  • 1
  • 1
  • 10
  • Why don't you use the DBI and related modules? If you don't, you have to write a lot of code to avoid it – Miguel Prz Feb 25 '13 at 07:34
  • 3
    Thre really isn't a proper alternative to the `DBI` module for accessing databases. There are a few modules that subclass it to provide additional facilities, but nothing that avoiuds it altogether. Why do you want to write your program without it? – Borodin Feb 25 '13 at 07:54
  • I have seen in a blog post that not every client will allow to intall the CPAN modules ......if that the case im looking for alternative . Hmm... but the same table data i can take in shell and could manipulate it to my use , so i was wondering if that table data could be sent to array or hash and get my required O/P – mviswa Feb 25 '13 at 08:21
  • Don't look for work-arounds for imaginary problems. Try to install `DBI` and `DBD::DB2` and only look for other solutions when you have tried *very hard* and failed to do it the proper way. – Borodin Feb 25 '13 at 23:35

2 Answers2

0

If your clients are requiring that you connect to a database, then they have to allow you to use the appropriate drivers to connect to the database. Anything else is insanity. In short: use DBI.

If it is a question of getting root access, you can install the modules locally to your script, without changing the system's Perl installation. See this: How can I install Perl modules without root privileges?

Of course, you could create something that obtained the output of a shell command using backquotes, then processed it with regexes. However, I recommend against that--unless connecting to the database is not the main purpose of your program, and you only need to do this one exact query. In that case, perhaps it would be easier to do that then install the modules.

Community
  • 1
  • 1
0

Retrieve the data with mysql client in csv format (tab separated) and pipe it to a perl oneliner is do the trick. You could format the output if you like.

db2...| perl -lne '@t=split(/\t/);print $_ if $t[2] >= 2000;'
user1126070
  • 5,059
  • 1
  • 16
  • 15