Accessing DB2 Mainframe (z/OS) from PHP

To access DB2 on Mainframe (z/OS), no special DB2 library for PHP is required. However, a DB2 ODBC driver must be downloaded from IBM and installed. A driver may be downloaded from the IBM web site:

http://www-933.ibm.com/support/fixcentrl/swg/downloadFixes

For example, "v9.,7fp11_ntx64_odbc_cli.zip" may be used.

After downloading it, unzip it in C:\Program Files\IBM.

Once unzipped, execute the following on the command line as the system administrator:

C:> cd "\Program Files\IBM\clidriver\bin"
C:> db2cli install -setup

Use the Administrative tool "Data Sources (ODBC)" to ensure that the driver is installed properly. If not, the command can be executed again. Since db2cli is not very verbose, it may be necessary to do it again. Also, note that if you run it as a regular user, it will display the success message but the installation does not occur.

Ensure that all components are using the same bit-level. For 32-bit PHP, all drivers must be in 32-bit. For 64-bit, all drivers must be in 64-bit. Also, ensure that php.ini contains "extension=php_odbc.dll" is included for Windows.

Once the installation is done successfully, you can then connect to DB2 using odbc_connect function as shown here:

<?php
$conn = odbc_connect('Driver={IBM DB2 ODBC DRIVER - C_PROGRA~1_IBM_CLIDRI~1};'
      . 'Database=database;Hostname=hostname;Port=port;'
      , 'username', 'password');
if ($conn === False) {
    die("Failed to connect: " . odbc_errormsg() . "\n");
}
$res = odbc_exec($conn, 'SELECT 9 AS NUM FROM SYSIBM.SYSDUMMY1');
if ($res === False) {
    echo "Exec failed: " . odbc_errormsg() . "\n";
}
?>
Number of fields: <?php echo odbc_num_fields($res); ?>  
Field name: <?php echo odbc_field_name($res, 1); ?>   
Fetching...
<?php
odbc_fetch_row($res);
$val = odbc_result($res, 1);
?>

From here, all of the ODBC functions may be used to interact with DB2.

Invoking External Commands within PowerShell

There are several ways to execute commands within PowerShell. One can use Invoke-Command cmdlet and the "&" call operator. But these cannot easily be used if the commands are built dynamically. Suppose that you are trying to gather up file names matching certain criteria with Get-ChildItem. Then you want to pass the collected names to another command depending on certain options.

Once you have the file list collected into an array, it can be passed as arguments to a command. For example:

$fileList = @()
Get-ChildItem -path "some-path" | ForEach-Object {
    if ($_.Length -gt 10MB) {
        $fileList += $_.FullName
    }
}
# now execute an external command
& zip.exe output.zip $fileList

A very nice bonus of using this method is that when $fileList is passed to the call operator, $fileList is expanded with spaces between items and quotes around the items that contain one or more spaces. For example, if $fileList contains two items

C:\Program Files\Paint\Paint.exe
D:\Temp\abc.txt

$fileList is going to be expanded as:

"C:\Program Files\Paint\Paint.exe" D:\Temp\abc.txt

The other options to use is Invoke-Expression cmdlet. However, it's notoriously difficult to build strings from an array to have quotes around the items that contain spaces.

Sending HTML Email from Perl

Sending email from perl is relatively an easy exercise. To send text email, you just need to use Net::SMTP like the following:

use Net::SMTP; sub sendmail($$@) { my $subj = shift; my $msg = shift; my $smtp = Net::SMTP->new($o_smtpsvr); $subj = "Subject: $subj\n" unless $subj =~ /^Subject: /; $smtp->hello('MyDomain'); $smtp->reset(); $smtp->help(''); $smtp->mail('noreply@xyz.com'); $smtp->to(@_); $smtp->data($subj . $msg); $smtp->dataend(); $smtp->quit; } sendmail('hello, world', 'my message', ('abc@xyz.com'));

If you wish to send HTML email instead, it gets a bit more complicated. The reason is that the message must be formatted in MIME. To do so, it's easier to use Email::MIME module from CPAN. Note that Email:MIME is not included with the standard perl distribution while Net::SMTP is.

use Email:MIME; use Net::SMTP; my $email_body =<<EOM; <html> <body> Hello, world! </body> </html> EOM my $email = Email::MIME->create( header_str => [ To => $email_to, Cc => $email_cc, From => $email_from, Subject => $email_subject ], body_str => $email_body, attributes => { content_type => 'text/html', charset => 'utf8', encoding => 'quoted-printable' } ); my $smtp = Net::SMTP->new($SmtpHostName); $smtp->hello('MyDomain'); $smtp->reset(); $smtp->help(''); $smtp->mail($email_from); $smtp->to($email_to); $smtp->data($email->as_string); $smtp->dataend(); $smtp->quit;

Linux: Zipping Directories into Zipped Files Recursively

Here's a perl script to do it. The idea is from using find2perl, which can construct how perl would do Unix/Linux find command. Using it as a base, a simple script can be very powerful. Note the use of $prune. This is equivalent to find's -prune option. It is used to stop traversing below the current directory.

1. #!/usr/bin/perl -w
2. #
3. # sqzfps.pl -- compress FPS batch files
4. #
5. # CJKim, 27-Jul-2016, Created
6. #
7. 
   
8. use strict;
9. use File::Find ();
10. use vars qw/*name *dir *prune/;
11. *name   = *File::Find::name;
12. *dir    = *File::Find::dir;
13. *prune  = *File::Find::prune;
14. 
    
15. my $topdir = 'some-top-directory';
16. my $aging  = 90; # 365;
17. my $cputhr = 50;
18. my $cpuchk = 50;
19. my $dircnt = 0;
20. $| = 1;
21. 
    
22. sub cpu_ok
23. {
24.     $dircnt++;
25.     if ($dircnt > $cpuchk) {
26.         $dircnt = 0;
27.         while (1) {
28.             open CPU, 'vmstat 1 2 | awk \'{if (++c == 4) print $15;}\' |';
29.             my $idle = <CPU>;
30.             close CPU;
31.             $idle += 0;
32.             last if $idle > $cputhr;
33.             print "Sleeping for 30 seconds since CPU is at $idle% idle...\n";
34.             sleep 30;
35.         }
36.     }
37.     return 1;
38. }
39. 
    
40. sub wanted
41. {
42.     # $_ is the file name only
43.     # $name is the full path
44.     # $dir is just the directory name
45.     # $prune may be set to 1 to stop traversing below the current dir
46. 
    
47.     my ($dev,$ino,$mode,$nlink,$uid,$gid);
48. 
    
49.     (($dev,$ino,$mode,$nlink,$uid,$gid) = lstat($_))
50.     && (-d _)  # directory only
51.     && (int(-M _) > $aging)  # so many days old
52.     && ($_ =~ /^\d{16}FF$/)  # fits the name pattern
53.     && ($prune = 1)  # stop traversing after this one
54.     && cpu_ok()  # check if we have enough cpu available
55.     && system "(echo 'Processing $_'; cd $name && zip -m $dir/$_.zip *.* && cd / && rmdir $name)"
56.     ;
57. }
58. 
    
59. 
    
60. # traverse the filesystem
61. File::Find::find({wanted => \&wanted}, $topdir);
62. 
    

Running the Same Command Periodically and Watch the Output

Have you ever find yourself typing the same command over and over again just to see what's been changing? On a standard Linux, there is a little-known utility called "watch". It can run a command repeatedly every so many seconds and shows you the output. But it goes one step further by showing the differences between the last run and current by highlighting the differences. Yes, it's a clunky command line tool but does a beautiful job of saving a lot of typing and figuring out what changed. See the man pages for watch(1) for the detail and the option flags.

I often used it with SQL*Plus to see the changes in database. For example, you can have a SQL statement in a file called, say, count.sql. You can invoke it using SQL*Plus but you will see the output only one time. To see the output periodically, you can invoke it with watch(1), e.g.

watch -d sqlplus scott/tiger@xe @count

Don't forget to have "EXIT" as the last command in count.sql so that sqlplus would exit after executing the SQL statements.

Cloning a Directory Tree in Linux/Unix

Here's an old command that clones an entire directory tree in Unix or Linux. So far, the command appears to work on all variants of Unix (HP-UX, Solaris, AIX, RedHat, Ubuntu, CentOS, and Fedora):

    $ cd to_the_directory_to_be_cloned  
    $ find . -depth -print | cpio -pdv destination_directory  

While the above command clones all files, since find(1) offers many options, you can clone selected files. For example, if you wish to clone only java programs, specify the option "-name '*.java'" to do so.

Accessing DB2 Mainframe (z/OS) from PowerShell

To access DB2 on Mainframe (z/OS), a DB2 ODBC driver must be downloaded from IBM and installed.
A driver may be downloaded from the IBM web site:

http://www-933.ibm.com/support/fixcentrl/swg/downloadFixes

For example, "v9.,7fp11_ntx64_odbc_cli.zip" may be used.

After downloading it, unzip it in C:\Program Files\IBM.

Once unzipped, execute the following in the command line as the system administrator:

C:> cd "\Program Files\IBM\clidriver\bin"
C:> db2cli install -setup

Use the Administrative tool "Data Sources (ODBC)" to ensure that the driver is installed properly. If not, the command can be executed again. Since db2cli is not very verbose, it may be necessary to do it again. Also, note that if you run it as a regular user, it will display the success message but the installation does not occur.

Once you have it, you can then connect to DB2 using System.Data.Odbc.OdbcConnection as shown here:

$db2conn = New-Object System.Data.Odbc.OdbcConnection
$cstr = "Driver={IBM DB2 ODBC DRIVER - C_PROGRA~1_IBM_CLIDRI~1};"
$cstr += "Database=DB2Database;Hostname=DB2Server;Port=5000;"
$cstr += "UID=DB2User;PWD=DB2Pswd;"
$db2conn.ConnectionString = $cstr
$db2conn.Open()

From here, you can use all the ODBC functions to interact with DB2.

A prepared statement can be used with ODBC. To do so, use OdbcCommand() to submit a query with bind variables specified with ? for each. For strings, there is no need to surround it with quotes. Then specify the value for them with Parameters.Add(). The first Parameters.Add() corresponds to the first ?, the second to the second ?, and so on. The variable types can be specified with [System.Data.Odbc.OdbcType] enumerator. The supported column types are: BigInt, Binary, Bit, Char, Date, DateTime, Decimal, Double, Image, Int, NChar, NText, Numeric, NVarChar, Real, SmallDateTime, SmallInt, Text, Time, Timestamp, TinyInt, UniqueIdentifier, VarBinary, and VarChar.

    $qry = @'
SELECT col1, col2, ..., coln
  FROM table
 WHERE col1 LIKE ?
'@
    $stmt = New-Object System.Data.Odbc.OdbcCommand($qry, $db2conn)
    $stmt.Parameters.Add('@col1', [System.Data.Odbc.OdbcType]::varchar, 20).Value = "$col1%"
    try {
        $xr = $stmt.ExecuteReader()

        while ($xr.Read()) {
            # do something with the returned data, which is the form of
            # $xr.GetValue(column-number-starting-at-0)
        }
    }
    catch {
        Write-Host "Executing DB2 $qry failed"
        Write-Host $Error[0].Exception
        Exit(-2)
    }
    $stmt.Dispose()