HOWTO: IBM DB2, ODBC, and Linux (*nix) configuration

The DB2 ODBC driver works like anything else IBM. Nonstandard to the rest of the universe. How to set up ODBC:

Unix admin needs to do:
Retrieve the config file locations:

$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/jason/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

/etc/odbcinst.ini : list of the available drivers and default configuration for each driver
/etc/odbc.ini : list of the instances/databases for *all* users aka “System DSNs”
${HOME}/.odbc.ini : list of the instances/databases specific to *you* aka “User DSNs”

Add/update the following lines in the /etc/odbcinst.ini file (or wherever the file is as reported by odbcinst -j). Make sure there is no whitespace at the beginnings of the lines.

[DB2]
Description = DB2 Driver
Driver = /opt/ibm/db2/db2_v9.7/lib64/libdb2o.so
fileusage=1
dontdlclose=1

The driver line should point to the full path of libdb2o.so (any *nix but aix) or libdb2o.a (aix).

Add/update the following lines in the ${HOME}/.odbc.ini file:

[MYDB2DB]
Driver=DB2

You need to catalog the database (assuming you already cataloged the node [db2host in my case] – see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001944.html):

 # catalog database database_name as database_alias at node node_name
catalog database MYDB2DB as MYDB2DB at node db2host

Test connectivity to the remote database:

db2 connect to MYDB2DB user testuser using test_password

Ensure we’re using the unixODBC isql:

$ /usr/bin/isql --version
unixODBC 2.2.14

Validate using unixodbc “isql”:

$ /usr/bin/isql -v MYDB2DB testuser test_password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

The DSN for the entry is simply “dbi:ODBC:MYDB2DB” if you’re using Perl. Later versions of the db2 client allow you to specify the connect options in the DSN like so:

my $odbc_connect_str = "dbi:ODBC:"
               . "DRIVER=/opt/ibm/db2_v10.5/lib64/libdb2o.so;
               . "Protocol=tcpip;"
               . "Hostname=mydbhost;"
               . "Port=67033;"
               . "DATABASE=MYDB2DB";
Share Button

Save lives using your computer and phone with the World Community Grid

World Community Grid enables anyone with a computer, smartphone or tablet to donate their unused computing power to advance cutting-edge scientific research on topics related to health, poverty and sustainability. Through the contributions of over 650,000 individuals and 460 organizations, World Community Grid has supported 28 research projects to date, including searches for more effective treatments for cancer, HIV/AIDS and neglected tropical diseases. Other projects are looking for low-cost water filtration systems and new materials for capturing solar energy efficiently.

Help save lives today and join my team Apraxia Kids

Share Button

Hercules/Mainframe: A tour of RPF under IBM’s MVS by Marcello Magnifico

Video by Marcello Magnifico

Share Button

IBM DB2 10 for z/OS manuals fixed to work with tablet/phone PDF readers like Aldiko

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields. In the case of the manuals for IBM DB2 v10 for z/OS, I’m unable to load the manuals for say v9.7 as they have the same Title/Author data.  I used the same method from SAP Sybase IQ 15.4 manuals fixed to work with tablet/phone PDF readers like Aldiko

Here are the IBM DB2 v10 for z/OS manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs on IBM’s website with the exception of the PDF fields I mentioned previously.

NO COPYRIGHT INFRINGEMENT IS INTENDED. IBM, PLEASE FEEL FREE TO TAKE THESE AND HOST THEM.

IBM DB2 v10 for z/OS – Command Reference

IBM DB2 v10 for z/OS – Data Sharing Planning and Administration

IBM DB2 v10 for z/OS – DB2 SQL Reference for Cross-Platform Development Version 4.0

IBM DB2 v10 for z/OS – Installation and Migration Guide – IBM

IBM DB2 v10 for z/OS – Internationalization Guide

IBM DB2 v10 for z/OS – Introduction to DB2 for z/OS

IBM DB2 v10 for z/OS – Installation, Administration, and Reference

IBM DB2 v10 for z/OS – IRLM Messages and Codes

IBM DB2 v10 for z/OS – Licensed Program Specifications

IBM DB2 v10 for z/OS – Managing Performance

IBM DB2 v10 for z/OS – Managing Security

IBM DB2 v10 for z/OS – Messages

IBM DB2 v10 for z/OS – ODBC Guide and Reference

IBM DB2 v10 for z/OS – pureXML Guide

IBM DB2 v10 for z/OS – RACF Access Control Module Guide

IBM DB2 v10 for z/OS – SQL Reference

IBM DB2 v10 for z/OS – User’s Guide and Reference

IBM DB2 v10 for z/OS – Utility Guide and Reference

IBM DB2 v10 for z/OS – What’s New?

IBM DB2 v10 for z/OS – Administration Guide

IBM DB2 v10 for z/OS – Application Programming and SQL Guide

IBM DB2 v10 for z/OS – Application Programming Guide and Reference for Java

IBM DB2 v10 for z/OS – Codes

Share Button

IBM Tivoli Storage Manager (TSM) – How to determine management class and retention using dsmc

If your company uses IBM’s Tivoli Storage Manager, you may one day be asked to determine what the management class or retention is for the backups you’re performing. Unless you are using the Windows GUI front end, you may be at a loss to obtain this information. Never fear, as long as you have access to the dsmc command line, this is easy to get.

tsm> query mgmtclass -detail

In the output, we can see that the default management class being used in “DFLT_MC”. Unless your backup job explicitly sets the management class, you will use “DFLT_MC”. The retention is 45 days.

Domain Name               : EXAMPLE
Activated Policy Set Name : EXAMPLE_POL
Activation date/time      : 01/19/2008 15:22:23
Default Mgmt Class Name   : DFLT_MC
Grace Period Backup Retn. : 45 day(s)
Grace Period Archive Retn.: 45 day(s)

MgmtClass Name                  : 90DAY_STOR
Description                     :
Space Management Technique      : None
Auto Migrate on Non-Usage       : 0
Backup Required Before Migration: YES
Destination for Migrated Files  : TAPEPOOL
Copy Group
        Copy Group Name........: STANDARD
        Copy Type..............: Archive
        Copy Frequency.........: Cmd
        Retain Version.........: 90 day(s)
        Copy Serialization.....: Shared Static
        Copy Mode..............: Absolute
        Retain Initiation......: Create
        Retain Minimum.........: 65534 day(s)
        Copy Destination.......: TAPEPOOL
        Lan Free Destination...: NO
        Deduplicate Data.......: NO

MgmtClass Name                  : DFLT_MC
Description                     :
Space Management Technique      : None
Auto Migrate on Non-Usage       : 0
Backup Required Before Migration: YES
Destination for Migrated Files  : BACKUPPOOL
Copy Group
        Copy Group Name........: STANDARD
        Copy Type..............: Backup
        Copy Frequency.........: 0 day(s)
        Versions Data Exists...: 6 version(s)
        Versions Data Deleted..: 45 version(s)
        Retain Extra Versions..: 45 day(s)
        Retain Only Version....: 70 day(s)
        Copy Serialization.....: Shared Static
        Copy Mode..............: Modified
        Copy Destination.......: BACKUPPOOL
        Lan Free Destination...: NO
        Deduplicate Data.......: NO

        Copy Group Name........: STANDARD
        Copy Type..............: Archive
        Copy Frequency.........: Cmd
        Retain Version.........: 45 day(s)
        Copy Serialization.....: Shared Static
        Copy Mode..............: Absolute
        Retain Initiation......: Create
        Retain Minimum.........: 65534 day(s)
        Copy Destination.......: TAPEPOOL
        Lan Free Destination...: NO
        Deduplicate Data.......: NO
Share Button

IBM DB2 LUW: What are my database configuration parameters?

IBM’s DB2 (Linux, Unix, Windows) is an db2logoodd beast. Well, if you’re used to any other DBMS on the planet, DB2 is odd and weird. But then, if you’re used to DB2 LUW, then the rest of the planet is smoking stuff. 😉

DB2’s configuration largely comes in two chunks: database manager configuration and database configuration. It might be easiest to think of the database manager as the parent entity that handles connections and the like (e.g. Oracle listener) and the database configuration as the instance configuration. There is a bit more to it than that but let’s focus shall we? Damn it all the helm! I left the Girl Scout cookies on the fridge at home where the kids couldn’t see them!

To pull the database manager configuration, connect to your DB2:

$ db2
db2 => connect to GOOBER

   Database Connection Information

 Database server        = DB2/AIX64 9.7.6
 SQL authorization ID   = DB2
 Local database alias   = GOOBER

db2 => GET DATABASE MANAGER CONFIGURATION

          Database Manager Configuration

     Node type = Database Server with local and remote clients

 Database manager configuration release level            = 0x0d00

 CPU speed (millisec/instruction)             (CPUSPEED) = 2.834065e-07

 Max number of concurrently active databases     (NUMDB) = 5
 Federated Database System Support           (FEDERATED) = YES
....
 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
 No. of int. communication channels   (FCM_NUM_CHANNELS) = AUTOMATIC(512)
 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10

To pull the database configuration it’s almost identical, connect to your DB2:

$ db2
db2 => connect to GOOBER

   Database Connection Information

 Database server        = DB2/AIX64 9.7.6
 SQL authorization ID   = DB2
 Local database alias   = GOOBER

db2 => GET DATABASE CONFIGURATION

       Database Configuration for Database

 Database configuration release level                    = 0x0d00
 Database release level                                  = 0x0d00
....
 SMTP Server                               (SMTP_SERVER) =
 SQL conditional compilation flags         (SQL_CCFLAGS) =
 Section actuals setting               (SECTION_ACTUALS) = NONE
 Connect procedure                        (CONNECT_PROC) =
Share Button

SAP Sybase IQ: SQLCODE=-1010000, ODBC 3 State=”HY000″: symbolic link to RAW device “already exists”

When you create an instance of IQ, you should use RAW devices for stability and performance. I recently created an instance and ran into the error SQLCODE=-1010000, ODBC 3 State=”HY000″ The file ‘/IQ/myiq/devices/IQ_MAIN/myiqmain001.iq’ already exists on AIX. The symbolic link /IQ/myiq/devices/IQ_MAIN/myiqmain001.iq was pointing to the RAW device /dev/myiqmain001. After many long hours on the phone with SAP, we found the solution, I’m rather embarrassed to say.

On AIX:

  • buffered (cooked) partitions are /dev/myiqmain001
  • unbuffered (REAL) RAW devices are /dev/rmyiqmain001

Note the letter “r” designating a RAW device.

When in doubt, you can use the file utility:
Block Special effectively means cooked partition:

file /dev/myiqmain001 
/dev/myiqmain001: block special (35/1)

Character Special means RAW device:

file /dev/rmyiqmain001
/dev/rmyiqmain001: character special (35/1)

Thanks to John Ting, Richard Weisbrod and Peter Bavin of SAP! It took a long time but we finally figured out what that something really really simple we’re overlooking was.

Feature Request 747716: for IQ to detect non-character raw device during db OR dbfile creation.

Share Button

IBM DB2 10.1 LUW manuals fixed to work with tablet/phone PDF readers like Aldiko

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields. In the case of the manuals for IBM DB2 LUW 10.1, I’m unable to load the manuals for say v9.7 as they have the same Title/Author data.  I used the same method from SAP Sybase IQ 15.4 manuals fixed to work with tablet/phone PDF readers like Aldiko

Here are the IBM DB2 10.1 LUW manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs on IBM’s website with the exception of the PDF fields I mentioned previously.

NO COPYRIGHT INFRINGEMENT IS INTENDED. IBM, PLEASE FEEL FREE TO TAKE THESE AND HOST THEM.

DB2 10.1 LUW – Preparation Guide for DB2 10.1 Fundamentals Exam 610 – IBM, Inc_
DB2 10.1 LUW – Command Reference – IBM, Inc_
DB2 10.1 LUW – DB2 Connect User’s Guide – IBM, Inc_
DB2 10.1 LUW – Database Security Guide – IBM, Inc_
DB2 10.1 LUW – Developing ADO.NET and OLE DB Applications – IBM, Inc_
DB2 10.1 LUW – Data Movement Utilities Guide and Reference – IBM, Inc_
DB2 10.1 LUW – Spatial Extender User’s Guide and Reference – IBM, Inc_
DB2 10.1 LUW – Call Level Interface Guide and Reference Volume 1 – IBM, Inc_
DB2 10.1 LUW – SQL Procedural Languages_ Application Enablement and Support – IBM, Inc_
DB2 10.1 LUW – Call Level Interface Guide and Reference Volume 2 – IBM, Inc_
DB2 10.1 LUW – Developing Embedded SQL Applications – IBM, Inc_
DB2 10.1 LUW – SQL Reference Volume 1 – IBM, Inc_
DB2 10.1 LUW – Getting Started with Database Application Development – IBM, Inc_
DB2 10.1 LUW – Developing Java Applications – IBM, Inc_
DB2 10.1 LUW – Developing Perl, PHP, Python, and Ruby on Rails Applications – IBM, Inc_
DB2 10.1 LUW – SQL Reference Volume 2 – IBM, Inc_
DB2 10.1 LUW – Text Search Guide – IBM, Inc_
DB2 10.1 LUW – Developing User-defined Routines (SQL and External) – IBM, Inc_
DB2 10.1 LUW – Upgrading to DB2 Version 10.1 – IBM, Inc_
DB2 10.1 LUW – Globalization Guide – IBM, Inc_ DB2 10.1 LUW – What’s New for DB2 Version 10.1 – IBM, Inc_
DB2 10.1 LUW – Installing IBM Data Server Clients – IBM, Inc_
DB2 10.1 LUW – DB2 Connect Installing and Configuring DB2 Connect Personal Edition – IBM, Inc_
DB2 10.1 LUW – DB2 Connect Installing and Configuring DB2 Connect Servers – IBM, Inc_
DB2 10.1 LUW – DB2 Workload Management Guide and Reference – IBM, Inc_
DB2 10.1 LUW – XQuery Reference – IBM, Inc_
DB2 10.1 LUW – Installing DB2 Servers – IBM, Inc_
DB2 10.1 LUW – Message Reference Volume 1 – IBM, Inc_
DB2 10.1 LUW – Configuration Guide for Federated Data Sources – IBM, Inc_
DB2 10.1 LUW – Message Reference Volume 2 – IBM, Inc_
DB2 10.1 LUW – Administration Guide for Federated Systems – IBM, Inc_
DB2 10.1 LUW – Precompiler Customization – IBM, Inc_
DB2 10.1 LUW – Replication and Event Publishing Guide and Reference – IBM, Inc_
DB2 10.1 LUW – SQL Replication Guide and Reference – IBM, Inc_
DB2 10.1 LUW – ASNCLP Program Reference for Replication and Event Publishing – IBM, Inc_
DB2 10.1 LUW – Administrative API Reference – IBM, Inc_
DB2 10.1 LUW – Database Monitoring Guide and Reference – IBM, Inc_ DB2 10.1 LUW – Net Search Extender Administration and User’s Guide – IBM, Inc_
DB2 10.1 LUW – Getting Started with DB2 Installation and Administration on Linux and Windows – IBM, Inc_
DB2 10.1 LUW – Database Administration Concepts and Configuration Reference – IBM, Inc_
DB2 10.1 LUW – Partitioning and Clustering Guide – IBM, Inc_
DB2 10.1 LUW – Troubleshooting and Tuning Database Performance – IBM, Inc_
DB2 10.1 LUW – Administrative Routines and Views – IBM, Inc_
DB2 10.1 LUW – pureXML Guide – IBM, Inc_
DB2 10.1 LUW – Preparation Guide for Exam 611 – IBM, Inc_
DB2 10.1 LUW – Data Recovery and High Availability Guide and Reference – IBM, Inc_

Share Button

Sybase’s PowerBuilder v12 is powerful, .NET based, and wonderful! Why you shouldn’t use it

The following is MY perception of Sybase’s PowerBuilder:

Years ago PowerBuilder was king.  No one could touch it.  It was relatively inexpensive.  Microsoft’s Visual Basic matured and the Pascal based Borland’s Delphi was released.  Then it fell and fall it did.

Sybase
Sybase

As it was falling from the throne Sybase purchased Powersoft, makers of PowerBuilder.  As the the market share continued to shrink, PowerBuilder developers had more difficulty in finding new projects.  Most new development was written in Visual Basic or Java.

Years went by with marketing of PowerBuilder little more than the occasional road show, TechWave presentations and the ISUG Technical Journal ads catered towards existing customers.  Little to no effort was put forth by Sybase to gain new PowerBuilder customers.

During this week’s Sybase TechWave, PowerBuilder version 12 was released.  It has all the whistles and kitchen sinks you could ask for.   An amazing tool for development!  Too bad no one outside of the die hard PowerBuilder programmers will use it.

Blasphemy!  Heretic!

Consider this:

Sybase owns PowerBuilder.  It owns the PowerBuilder software, PowerBuilder language, PowerScript, the PowerBuilder vm, and everything PowerBuilder.

No problem right?

What will happen to PowerBuilder when Sybase is bought out by another company?  Products with tiny market share like PowerBuilder would likely be killed or in a state of limbo for several years.  Anyone remember what happened when IBM bought Informix?

Do you really want to bet your career and business on a software development tool that is locked to a single smallish vendor?

Maybe, perhaps, if Sybase were to release the PowerBuilder 4GL language and PowerScript to the world like Microsoft did with the C# and Visual Basic languages and Sun with Java…   Perhaps if Sybase would allow 3rd parties to develop tools based around the PowerBuilder language royalty free…

Sybase:  PLEASE FREE THE POWERBUILDER 4GL LANGUAGE!

I mean, really, what benefit could Sybase have to cripple the PowerBuilder developers?

Share Button

Native support of SQLite in Sybase PowerBuilder and PowerDesigner?

What I would love to see is native support for SQLite. http://www.sqlite.org

It is the most installed embedded database on the planet hands down.  Don’t believe me?  You know that Firefox web browser, Thunderbird newsreader, most Adobe products, Miro, etc all have it embedded?  – reference http://www.sqlite.org/mostdeployed.html

We use it extensively at work as:

  1. staging for mass data imports/exports/conversions
  2. local application ‘cache’ for large data sets
  3. projects that don’t require all the features of Sybase ASE (or Oracle for that matter)

One of the best features is that the database itself in platform independent… copy the db on to AIX from your Windows box … then on to your old Amiga … then on to your windows mobile device.  Getting the point?  🙂

I really wish Sybase would make it so that the Sybase ASE databases were truly platform and character set/sort order independent… but that is in another dream 😉

Not a blurb in the PowerDesigner/PowerBuilder manuals or anything

Share Button