Frequently Used DB2 Functions and Expressions

Get the number of days since 0001-01-01

SELECT DAYS('2016-12-25')

Convert date string to datetime type

SELECT date(to_date(string_date_column,'YYYY-MM-DD'))

Calculate difference in number of days between two dates

SELECT DAYS('2016-01-01') - DAYS('2015-01-01')

Get day of the month, month, and year

SELECT DAY('2016-12-25'), MONTH('2016-12-25'), YEAR('2016-12-25')

From DB2 v10, dates can be formatted with a desired format string

SELECT VARCHAR_FORMAT(date-column, 'YYYY-MM-DD HH:MI:SS')

Frequent Regular Expressions

Here are some frequently used regular expressions.

IP Address:
^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$

Social Security Number:
^\d{3}-?\d{2}-?\d{4}$

Credit Card: [Visa, MC, AmEx, and Discover]
^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35\d{3})\d{11})$

Use Luhn algorithm to check if a credit card number is a valid number. Of course, the algorithm doesn't obviously check if the card is actually any good. Such must be checked with the card issuer.

Renting a Car in South Korea

Introduction:

I often traveled to South Korea. My trip was the first one for me to rent a car. Because the public transportation in Korea is ubiquitous, it's not really necessary to rental a car, especially when your destinations are in major cities like Seoul and Busan. Since there are many sites discussing how vast public transportation is in Korea, I wouldn't cover it here.

I tried to search for some information as what it's like to rent a car in Korea but couldn't find any information. Hence, it prompted me to write this blog.

International Driving Permit:

Before you leave, you must obtain an International Driving Permit. I obtained mine from a local AAA office. It's nothing more than a booklet with the information off of your own driver's license and your picture. AAA charges $20 (in 2016) for it and it is good for only one year. The valid duration is not determined by AAA but the international body that established International Driving Permit system. You can go to the AAA web site to obtain the application. Unless you want to pay extra to AAA, you may want to get your photo taken like the one you would for a passport.

Rental Companies:

There may be many companies offering rental cars in Korea; however, I tried only AVIS. The price was very reasonable. Note that AVIS is affiliated with AJ Rent-a-Car in Korea. Asking about AVIS at the airport isn't going to work too well as the folks there don't know AVIS. If you're renting a car from Hertz, you will find that Hertz is affiliated with Lotte Rent-a-Car. At the Incheon Airport, the rental car counters are located at both ends of the arrival terminal. However, you may want to go to the one near gate A instead. The one at the opposite end isn't always manned.

Rental Contract:

Note that the folks at the rental car counter will not speak English as fluently as you may wish. Please be specific as how you ask questions. Speak slowly whenever possible. You will be asked to present your passport and International Driving Permit. Your own driver's license is not much of use here. Without an International Driving Permit, you will NOT be able to rent a car. When you rent a car, you will be asked if you wish get a GPS (Koreans call it "Navi" pronounced more like "nebi" and not GPS). If you do not speak Korean, you want to make sure you get the English version. Without a GPS, it will be quite difficult to get around as the Korean road system has been evolving significantly. Also, Koreans are not really into telling you street or highway names but directions like turn left/right. Also, Korea uses the metric system. You should get some idea as what 100 meters is like.

My AVIS rental included a basic collision insurance that covers up to 300,000 wons, which is slightly less than $300. If you feel you need more, you should ask for a higher coverage. One other thing to note is that if you get into an accident and the damage takes few days to repair, you are directly liable for the days the car is out of service at the half of the daily rental rate. For example, if your daily rental rate is $20 and the two days are needed to repair the damage, you owe $20 ($20 * 50% * 2 days).

Driving:

Driving around is fairly easy. Nothing too tricky. The signs are posted both in Korean and English.

The speed limit is 80 to 100 kph on most of the highways. You should try and drive at the posted speed limit. All speedsters are caught by the traffic cameras. GPS usually warns of the upcoming traffic cameras. You should pay attention to what GPS is informing. A typical speed limit sign is:

There are many toll highways. You need to prepare a lot of money in Korean currency to pay the tolls.

On highways, often the left most lane is for buses. If you see a blue line for the left most lane, you don't want to drive in it. You will get a ticket. This situation is the only time when police actually pulls you over.

When you make a left turn, wait for a left arrow. You cannot turn left when it's green unless a sign is posted specifically to allow it. The signs are, unfortunately, only in Korean, which reads:

Right turn on red is allowed when it's safe to do so.

If you see diamond sign painted on the street, they simply mean that a traffic signal is ahead and not carpool lanes.

There are many tunnels in Korea since the 70% of Korea is mountainous. Many bridges and tunnels make the highways relatively flat.

Use your common sense to decide what to do in most cases. The traffic rules and signs are fairly logical.

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.