32-bit and 64-bit application development with DB2 UDB V8.2

If you are migrating from a 32-bit DB2® instance to a 64-bit one, deploying 32-bit applications to 64-bit platforms, or simply want to ensure that you can do so easily later, then this article is for you. We outline DB2 Universal Database's™ (DB2 UDB) 32-bit and 64-bit application and routine support and explain the considerations and best practices related to developing and deploying DB2 applications and routines to 64-bit platforms.

Share:

Gwyneth Evans (grevans@ca.ibm.com), Software developer, IBM Canada, Inc.

Gwyneth EvansGwyneth Evans is a software and information developer in the DB2 Information Development team for DB2 UDB for Linux, UNIX, and Windows at the IBM Software Lab in Toronto with a focus on SQL and application development. She has previously worked in product development for the DB2 UDB for Linux, UNIX, and Windows product and as an application developer.



07 October 2004

Terminology

Before discussing DB2's 32-bit and 64-bit support, it is important to first review and understand some terminology and essential concepts in the context of this article:

  • Platform: The basic technology of a computer's hardware and operating system that defines how a computer operates and what software can be run on it.
  • Hardware: A computer and its associated architecture.
  • Operating system: Software that controls a computer's hardware and enables other applications and users to use it. Operating systems consist of a kernel that interfaces directly with the hardware and a set of operating system libraries that are required to build and run applications.
  • DB2 instance: A logical database server environment in which DB2 databases are created.
  • Database client: A computer or software program that requests access to database objects and data, runs applications, or request application-based services from a database server.
  • Database server: A computer or program that contains and manages a database and that processes requests for database data made by clients.
  • 32-bit: Pertains to the number of binary digits (bits) processed by a computer in parallel or to represent memory addresses. For example, a 32-bit computer has data registers that are 32 bits wide and uses 32 bits to identify each address in memory.
  • 64-bit: See 32-bit. A 64-bit computer has data registers that are 64 bits wide and uses 64 bits to identify each address in memory.

Introduction

Whether you plan to migrate your database client or server to a 64-bit platform or simply want to build and deploy applications or routines to a 64-bit DB2 instance, it is important to understand DB2's 32-bit and 64-bit application development support. It is also important to adopt the best practices that will facilitate the deployment of your applications and routines and ensure that they function as expected in the target environment.

This article is divided into three parts: first, to introduce you to 32-bit and 64-bit object concepts, then help you gain knowledge about 32-bit and 64-bit DB2 configuration and feature support, and finally to help you develop strategies for developing and deploying applications that will work in 64-bit DB2 instances.


Part I: Overview of 32-bit and 64-bit objects

It is important to generally understand how 32-bit and 64-bit objects are related in a computing environment, since there can be several dependencies between them that can affect everything from what hardware you purchase to how you deploy your applications.

32-bit and 64-bit hardware

32-bit computer hardware uses 32 bits to represent memory addresses and to process instructions and data. 64-bit hardware uses 64 bits to do the same thing. In general, 32-bit operating systems run on 32-bit hardware and 64-bit operating systems run on 64-bit hardware, although it is possible to run 32-bit operating systems on some 64-bit hardware.

32-bit and 64-bit operating systems

Operating systems consist of a kernel that interfaces directly with the hardware and operating system libraries. Operating systems come with either a 32-bit or 64-bit kernel, or, in some cases, both. In general, 32-bit operating system kernels can exploit four gigabytes of real memory (the physical RAM shared by the operating system and the running applications), whereas 64-bit operating system kernels can exploit more than this. Of course, some 32-bit operating system kernels can exploit more than four gigabytes of memory, but don't do this as well as 64-bit kernels. 64-bit kernels may be required on some operating systems to run 64-bit applications, which cannot be run if a 32-bit kernel is in use. This is the case with all UNIX® operating systems, with the exception of AIX®. On AIX, a particular exception, you can run 32-bit and 64-bit applications with either a 32-bit or 64-bit kernel; however, to prevent scalability problems it is better to use the 64-bit kernel even when running 32-bit applications.

Operating system libraries are important, because they are required to build and run applications. To build 32-bit applications, you have to link to 32-bit system libraries. To build 64-bit applications you have to link to 64-bit system libraries. Having the 64-bit system libraries available in a particular operating system doesn't necessarily mean that the system is actually capable of running 64-bit applications. This is often true of the 32-bit Windows® operating system, which allows you to cross-compile and link 64-bit applications without being able to run them. This is also true of the UNIX platforms, because there are cases where you can install 64-bit-capable versions of operating systems on 32-bit hardware and cases where 32-bit kernels are incapable of running 64-bit applications. Essentially you can consider a 32-bit operating system to be one that only has the capacity to run 32-bit applications, whereas 64-bit operating systems can also run 64-bit applications, although this capability requires the use of 64-bit hardware and might additionally require the use of a 64-bit operating system kernel.

32-bit and 64-bit applications

32-bit applications are compiled such that memory addresses are 32-bit (four bytes) in size. These applications can directly exploit up to four gigabytes of virtual memory - the memory potentially available for use on a computer. This virtual memory constraint is present regardless of the amount of real memory (RAM) available on the system to be shared between the operating system and other applications. 64-bit applications on the other hand are compiled such that memory addresses are 64-bits (eight bytes) in size and can use more than four gigabytes of virtual memory without restriction. Operating systems typically also impose additional virtual memory restrictions on applications, and the theoretical maximum virtual memory per application may be as little as 1-2 gigabytes, even though it may have 32-bit addressing capability.

When you compile an application on a 32-bit or 64-bit platform, by default it is generally compiled to run on that particular platform. You can create 64-bit applications on 32-bit operating systems and 32-bit applications on 64-bit operating systems with some compilers by using special compiler-specific compile options and by appropriately linking to 32-bit or 64-bit libraries where appropriate.

32-bit applications can generally be run on both 32-bit and 64-bit operating systems, although many operating systems cannot run 64-bit applications if they employ a 32-bit kernel.

Overview of DB2's 32-bit and 64-bit objects

From a DB2 perspective, there certainly are 32-bit and 64-bit DB2 objects. 32-bit and 64-bit DB2 instances are examples of these. You can create 32-bit DB2 instances on either 32-bit or 64-bit operating systems, but you can only create 64-bit DB2 instances on 64-bit operating systems. Some hybrid 32-bit and 64-bit operating systems, such as AIX Version 5.1, can support both types of DB2 instances. Multiple DB2 instances can exist on a single database server to service different user needs (testing versus production instances) and do not have to be of the same bit type.

DB2 applications can be created as 32-bit or 64-bit objects in both 32-bit and 64-bit DB2 instances with most compilers, but to function properly the 32-bit or 64-bit DB2 applications must be linked respectively to DB2's 32-bit and 64-bit libraries, which are available in both types of DB2 instances.

32-bit DB2 external routines are DB2 external routines (procedures, user-defined functions (UDFs)) that load and run 32-bit external libraries when they are invoked. 64-bit external routines load and run 64-bit external libraries when invoked.

With a basic understanding of the relationship between 32-bit and 64-bit objects, it becomes easier to see how you must manage your DB2 application development practices so that your system can support 32-bit and 64-bit application development and deployment.


Part II: DB2 32-bit and 64-bit application and routine support

DB2 UDB truly is a universal database with respect to 32-bit and 64-bit cross-platform development. No other database provides as much support for developing and deploying applications on as many platforms as DB2. When it comes to 32-bit and 64-bit application development support DB2 leads the way. The following sections outline DB2's key 32-bit and 64-bit platform and application development support.

Supported 32-bit and 64-bit DB2 UDB products

The following DB2 Universal Database for Linux, UNIX, and Windows products are available for both 32-bit and 64-bit platforms:

Table 1. DB2 32-bit and 64-bit supported software
DB2 product
DB2 UDB Express Edition
DB2 UDB Personal Edition
DB2 UDB Personal Developer's Edition
DB2 UDB Developer's Edition
DB2 UDB Workgroup Server Edition
DB2 UDB Workgroup Server Unlimited Edition
DB2 UDB Enterprise Server Edition

You can develop database applications using Personal Developer's Edition or any of any of the other products provided that you install the DB2 Application Development Client on the client.

For more information about the DB2 products, extenders, tools, and other information management software products, please refer to: http://www.ibm.com/software/data/db2/udb.

Supported 32-bit and 64-bit platforms

Once you have determined which DB2 product meets your needs, you must select hardware for your database system. The following table outlines the supported 32-bit and 64-bit platforms that can be used as DB2 database servers or DB2 clients.

Table 2. Supported platforms
32-bit platforms64-bit platformsHybrid platforms
AIXAIXAIX hybrid (5.1)
HP PARSICHP PARSIC
HP IPFHP IPF
Linux Intel 32-bitLinux AMD
Linux PPCLinux EMt64
Linux zSeriesLinux Itanium 64-bit (IA64)
SolarisLinux PPC
Windows 32-bitLinux zSeries
Solaris
Windows IA64

Many factors can influence your choice of platform for your development and testing or production systems. Most often the decisions come down to price, performance, scalability, and stability. When it comes to choosing between 32-bit or 64-bit hardware platforms for a database server, you should consider the following benefits of using 64-bit platforms:

  • More memory available for building and running larger applications
  • More efficient data processing and better application performance

Very often the efficiency of a production system is highly critical to the success of a data management strategy, which is why faster 64-bit hardware is often used for database servers in production systems.

If you run separate development, test, and production systems, it is worth noting that application deployment is easiest when your development and production system are on the same platform, or are at least running the same operating system.

DB2 32-bit and 64-bit database connection support

You can mix and match the platforms that you choose for your DB2 database clients and servers, because DB2 supports local and remote connections to database servers from both 32-bit and 64-bit clients.

Local database connections are used to connect a DB2 client or client application to a DB2 database server that resides on the same computer as the client without requiring a network protocol. Remote database connections, on the other hand, connect to a DB2 server database that resides on a different computer, and thus requires a network protocol such as TCPIP. The following table shows DB2's complete support for both local and remote connections between 32-bit and 64-bit clients and 32-bit and 64-bit DB2 database servers.

Table 3. DB2 support for connections from 32-bit and 64-bit clients to 32-bit and 64-bit servers
DB2 client32-bit DB2 server (local and remote)64-bit DB2 server (local and remote)
32-bit clientSupportedSupported (1)
64-bit clientSupportedSupported

This broad support is an important feature if your DB2 clients and DB2 database servers are on different types of platforms. It also means that your 32-bit and 64-bit applications can connect and communicate with databases on both 32-bit and 64-bit database servers.

For a detailed description of DB2 supported 32-bit and 64-bit hardware and client-server configurations for DB2 Versions 7 and 8 , please refer to the topic, "Supported and non-supported client configurations," which can be found both in the DB2 Quick Beginnings Guide (PDF) and the Information Center (HTML).

DB2's 32-bit and 64-bit client application support

DB2 supports a variety of compilers, interpreters, and related development software for each of the supported platforms. You can build both DB2 32-bit and 64-bit applications in either a 32-bit or 64-bit DB2 instance with just about all of the DB2 supported compilers that provide 32-bit and 64-bit application compilation support.

Verify that the compiler or development software you require supports your 32-bit and 64-bit application development needs before you begin coding your applications. For a detailed description of DB2 supported development software for each hardware platform, please refer to the topic, "Supported development software" which can be found both in the DB2 Application Development Guide: Building and Running Applications (PDF) and the Information Center (HTML).

Before building and linking your applications as 32-bit or 64-bit DB2 applications, it is good to know exactly where you will be able to run them. The following table shows the hardware and operating systems on which DB2 32-bit and 64-bit client applications can run, provided that they were correctly compiled and linked:

Table 4. DB2 run time support for 32-bit and 64-bit applications on 32-bit and 64-bit platforms
Client application32-bit hardware + operating system with a 32-bit DB2 instance64-bit hardware + operating system with a 32-bit or 64-bit DB2 instance
32-bit client applicationSupportedSupported (1)
64-bit client applicationNot supportedSupported

(1) Windows 32-bit applications can run, as is, on Windows 64-bit platforms without any changes to the environment. On UNIX, you can deploy your existing 32-bit applications to all 64-bit platforms except Linux IA64 and Linux for zSeries®, by simply rebinding the applications and running them with the correct library path settings.

Recommendations on how to correctly build, link, and bind applications that are to be deployed to a different platform are discussed in more detail in Part III of this article.

DB2's 32-bit and 64-bit routine support

Routines (stored procedures, UDFs and methods) are distinct from applications. Routines – database objects that encapsulate database and programming logic - run on the database server and are created by executing routine specific CREATE statements that define the routine's characteristics.

SQL routine support

As of DB2 Version 8.2, SQL procedure definitions reside completely within the database like SQL function, table, trigger, and other database object definitions. They are no longer associated with any executable code that resides on the database server, which means there are no 32-bit or 64-bit related factors related to the creation, invocation, deployment, or migration of SQL routines.

External routine support

The routine-body of an external routine is written in a programming language and compiled into a library that is loaded and run when the routine is invoked. Two clauses in the CREATE statement for external routines, FENCED and NOT FENCED, determine whether the external routine runs in a fenced environment distinct from the database manager or in the same addressing space as the database manager. Generally, unfenced routines can perform better than fenced ones, because they communicate with the database manager via shared memory instead of TCPIP communications. By default routines are always created as fenced regardless of the other clauses selected.

The following table illustrates DB2's support for running fenced and unfenced 32-bit and 64-bit routines on 32-bit and 64-bit database servers that are running the same operating system:

Table 5. DB2 support for running procedures and UDFs on 32-bit and 64-bit servers
Routine type32-bit server64-bit server
32-bit fenced procedure or UDFSupportedSupported (1)(2)(3)
64-bit fenced procedure or UDFNot supported(4)Supported
32-bit unfenced procedure or UDFSupportedNot supported (2)
64-bit unfenced procedure or UDFNot supported (4)Supported

(1) Running a 32-bit routine on a 64-bit server is not as fast as running a 64-bit routine on a 64-bit server.
(2) 32-bit routines must be created as FENCED and NOT THREADSAFE to work on a 64-bit server.
(3) It is not possible to invoke 32-bit routines on Linux IA 64-bit database servers.
(4) 64-bit applications and routines cannot be run in 32-bit addressing spaces.

The important thing to note in Table 5 is that 32-bit unfenced procedures cannot run on a 64-bit DB2 server. If you must deploy 32-bit unfenced routines to 64-bit platforms, remove the NOT FENCED clause from the CREATE statements for these routines before you catalog them.

32-bit and 64-bit external routine resolution

When SQL statements that reference routines, such as the CALL statement used for invoking procedures or the SELECT query that can contain a column-function in the select-list, are executed, DB2 resolves the routine references based on the routine definitions in DB2's system catalog tables and invokes them as part of the complete SQL statement's execution.

When external routines are invoked, DB2 locates the routine's external class or library file on the database server by name, loads it, and runs it. On 32-bit platforms external routines are always loaded and run as 32-bit objects. On 64-bit platforms, for performance reasons, DB2 assumes that the target library for a routine invocation is a 64-bit object. Therefore, DB2 first attempts to execute a routine's external library or class as a 64-bit object. If this fails, DB2 then automatically attempts to load and run the library or class as a 32-bit fenced routine library.

If you have both 32-bit and 64-bit versions of a library or class for a routine on the database server file system, for optimal performance, you should make sure that the routine's CREATE statement identifies the correct external library or class file to be used instead of leaving DB2 to search for the correct version to be run.

For more about routine resolution and invocation, refer to: Routine invocation - an IBM DB2 documentation topic.

Performance of 32-bit external routines on 64-bit database servers

Since the choice to implement external routines is often based on the need to improve client application performance, getting maximal performance from them is often a priority. On 64-bit database servers, external routine performance is partly determined by whether your external routines load 32-bit or 64-bit external routine libraries.

With the exception of Java™ routines, invoking 32-bit routines on 64-bit servers does not perform as well as invoking 64-bit routines on 64-bit servers, because DB2, for performance reasons, first attempts to execute them as 64-bit objects before attempting to execute them as 32-bit objects, which requires special 32-bit fenced-mode processing. For a single 32-bit routine invocation, the overhead may appear to be negligible, but when this routine is invoked many times the overhead can become significant. If performance is a concern, it is recommended that you rebuild (compile, bind, and link) your routine source code to create 64-bit routine libraries.

Performance of 32-bit Java routines on 64-bit database servers

The good news regarding 32-bit Java routines running on 64-bit database servers is that if they are run using a 32-bit JVM, they perform as well as 64-bit Java routines using a 64-bit JVM and you do not require a 64-bit JVM.

32-bit Java Virtual Machines (JVM) can function on 64-bit DB2 database servers, which can be used to run 32-bit Java routines. 32-bit Java routines running on 64-bit database servers do not scale well, however, because they must be cataloged and run as FENCED NOT THREADSAFE, which means that every invocation of the routine requires its own JVM. If you anticipate needing a high degree of scalability from your routines, create these routines using a different programming language.

DB2 Development Center support on 32-bit and 64-bit platforms

The DB2 Development Center provides a graphical user interface for developing, building, debugging, and deploying SQL and Java routines and is available as an optional component of the DB2 Application Development Client. The DB2 Development Center is supported as one of the following client application types:

  • AIX 32-bit
  • AIX 64-bit
  • Linux Intel 32-bit
  • Linux IA2 64-bit
  • Sun 32-bit
  • Sun 64-bit
  • Windows 32-bit
  • Windows IA 64-bit

DB2 Development Center server support

The DB2 Development Center supports connections to the following DB2 database servers:

  • AIX 32-bit
  • AIX 64-bit
  • DB2 z/OS (V6)
  • DB2 z/OS (V7)
  • DB2 z/OS (V8)
  • iSeries (V5r2)
  • iSeries (V5r3)
  • HP-UX PA64
  • HPUX IPF64
  • Linux Intel 32-bit
  • Linux IA2 64-bit
  • Linux /390
  • Linux z/Series
  • Sun 32-bit
  • Sun 64-bit
  • Windows 32-bit
  • Windows IA 64-bit

DB2 Development Center routine development and debugging support

The DB2 Development Center supports connections to many DB2 servers and supports the building and debugging of SQL and Java (JDBC and SQLJ) routines for a large subset of those DB2 servers. The following table summarizes the DB2 Development Center's routine development and debugging support:

Table 6. DB2 Development Center 32-bit and 64-bit routine development and debugging support
Server platformSQL procedure developmentSQL procedure debuggingJava procedure developmentJava procedure debuggingSQL scalar & table UDF development
Windows 32-bitYes Yes Yes Yes(1) Yes
Windows IA64Yes Yes Yes NoYes
Linux Intel 32-bitYes Yes Yes No Yes
Linux IA2 64-bitYes Yes Yes No Yes
Sun 32-bitYes Yes Yes No Yes
Sun 64-bitYes Yes Yes No Yes
AIX 32-bitYes Yes Yes Yes(1) Yes
AIX 64-bitYes Yes Yes No Yes
Linux /390Yes No Yes No Yes
HP 32-bitYes Yes Yes No Yes
HP 64-bitYes Yes Yes No Yes
HP-UX 64-bitYes No Yes No Yes
Linux zSeriesYes No Yes No Yes
DB2 z/OS (V6)Yes No No No No
DB2 z/OS (V7)Yes No Yes No No
DB2 z/OS (V8)Yes Yes Yes No No
iSeries (V5r2)Yes No No No No
iSeries (V5r3)Yes No Yes No No

(1) Java stored procedures developed on this server can only be debugged from Windows 32-bit and AIX 32-bit Development Center clients.

With the Development Center, you can perform other 32-bit/ 64-bit independent application development activities, including creating and running: UDFs that can read WebSphere MQ® Queues, UDFs that access OLE DB data providers, and UDFs that work with XML data. Routines written in programming languages other than SQL or Java can be created and run from the command line and CLP. Such procedures can also be executed from Development Center's Server View. These procedures cannot be modified or debugged from Development Center. For a complete list of Development Center features and functionality refer to:

DB2 Development Center routine import, export, and deployment support

The DB2 Development Center provides an Import Wizard, an Export Wizard, and a Deployment Wizard that can be used to help you move routine definitions and deploy routines. The Import and Export Wizards can be used to move SQL or Java routine definitions in un-compiled form from a source machine to a target machine. Using these wizards you can also move compiled forms of the required routine libraries along with the source files.

Once you import a previously exported routine definition to a Development Center project, you can use the Deployment Wizard to deploy the SQL or Java routines. The deployment wizard provides options for either rebuilding the exported routine source or deploying the routine using the compiled form of the required library if it was imported with the routine definition.

The Deployment Wizard catalogs the routine on the target server and re-builds the routine libraries or classes on the target server if required. The Deployment Wizard provides an option for re-using, if applicable, existing routine binary libraries or classes, however if the target database server is running a different operating system than the source database server this is not possible and so instead the Deployment Wizard will rebuild the required libraries or classes.

SQL routine deployment support (non-Development Center support)

In DB2 Version 8.2, SQL procedures are natively implemented – that is, they are database objects that are completely implemented within the database manager upon the execution of their associated CREATE statement. No part of their implementation resides externally to the database manager. This means there are no 64-bit platform considerations when creating or deploying SQL procedures in DB2 Version 8.2. SQL functions have always been natively implemented and similarly have no 64-bit specific creation or deployment considerations.

Prior to DB2 Version 8.2, DB2's SQL stored procedures were implemented as embedded SQL C code that ran on the database server. These SQL procedures can be deployed to another computer by either re-creating the routine on the target machine or by moving the compiled form of the stored procedure using the GET ROUTINE and PUT ROUTINE commands. These commands are intended to be used when the target computer runs the same operating system and level of DB2 as the machine upon which the procedure was created, but does not have a C compiler installed. If you want to deploy a 32-bit SQL procedure created with a version of DB2 prior to DB2 Version 8.2 to a 64-bit target platform with the same version of DB2, you must explicitly re-create the SQL procedure on the target platform.

External routine deployment support (non-Development Center support)

Deploying external routines entails two main steps:

  • Cataloging the routine in the target database server by executing the routine's CREATE statement on the target server.
  • Optionally rebuilding and deploying the routine libraries or classes to the target server's function directory.

External routine libraries and classes are created in essentially the same way as client applications, except for the need for a few additional linker options. Deployment of routines without the Development Center is therefore very similar to that of client applications. The deployment of client applications is discussed in Part II of this document.


Part III: Developing and deploying portable 32-bit and 64-bit DB2 applications

Whether you want to develop 64-bit applications on your 32-bit DB2 instance, deploy 32-bit applications to a 64-bit instance, or simply protect yourself from any roadblocks to doing so down the road, you should follow the best practices that are outlined in the sections below.

Develop platform independent applications and routines

As a general rule, if you develop database applications or routines using a 32-bit DB2 instance and anticipate having to deploy them to a 64-bit DB2 instance, you should take special care to develop them so that they are as portable as possible. To make your applications and routines portable, use standardized platform-independent code. For example, if you adopt a habit of using a subset of the functionality available in the ISO, C specification will ensure a much higher chance of code portability. Certainly avoiding the use of platform specific methods or hard-coded file or directory path names in your code will also help. When you deploy your applications you don't want to discover that certain methods are not supported on the target platform or that because the target environment is different from the development one that file and path names are invalid.

Use DB2 portable data types for numeric host variables

In 32-bit and 64-bit applications, numeric data types can be represented with a different number of bits. For example, the size of the int and long data types in C or C++ programming is not specified. For all the UNIX platforms that DB2 supports, the long type is always represented with 32-bits in 32-bit applications and with 64-bits for 64-bit applications. In Windows, the long type always has a 32-bit representation. Table 7 shows how C and C++ data type requirements differ in 32-bit and 64-bit applications.

Table 7: Bit requirements for C and C++ data type representations in 32-bit and 64 bit applications
Type of applicationCharShortIntLongMemory Address
32-bit UNIX and Windows816323232
64-bit UNIX816326464
64-bit Windows816323264

When an application initially written for a 32-bit platform is rebuilt as a 64-bit application, the possibly different sizes of various data types may result in incorrect functionality. For example, when such an application is run you might encounter numeric data truncation, invalid data values due to truncation of memory addresses, or null pointer reference errors.

As an example of why you should use the recommended portable data types, consider the following example. Let's say you decide to use one of the following native data types to represent a BIGINT SQL data type:

  1. long long
  2. long
  3. __int64

You can use any of these data types to represent BIGINT, but then you must be willing to accept the following limitations on your application:

  1. If you use "long long", your application will only work on UNIX platforms
  2. If you use "long", your application will only work on 64-bit UNIX platforms
  3. If you use "__int64", your application will only work on Windows platforms

To prevent these kinds of portability problems, DB2 provides a set of portable host variable data types that you should use instead of platform-specific native types. The DB2 header file sqlsystm.h located in the SQLLIB\include (Windows) or sqllib/include/ (UNIX), directory contains the definitions of the available portable data types. In particular you should use these portable types for host variable declarations in your application. Figure 8 shows the recommended DB2 portable data type you should use for SQL host variables in your applications.

Table 7: Bit requirements for C and C++ data type representations in 32-bit and 64 bit applications
SQL data typeNative typeRecommended DB2 UDB portable type
SMALLINTshort
unsigned short
sqlint16
sqluint16
INTEGERIntegersqlint32
BIG INTEGERlong
unsigned long
sqlint64
DOUBLElong
unsigned long
sqlint32
DECIMALlong
unsigned long
sqlint32

To contrast C/ C++ with Java: In Java, long is always a 64-bit type and is therefore appropriate for BIG INTEGER values. As well, in Java, the integer data type is always represented with 32-bits and is therefore always appropriate for SQL INTEGER values.

For the complete set of recommended data types for each programming language refer to the related links in the Resources section at the end of this article.

Precompile C / C++ applications using LONGERROR YES option

To help you locate potentially problematic uses of long data types, in C and C++ code, DB2 provides the LONGERROR precompile option. When you precompile applications in a 32-bit DB2 instance with this option explicitly set to YES, DB2's precompiler returns an error each time it encounters a host variable of type long.

Precompiling application code in a 32-bit DB2 instance using the LONGERROR option is a good way of ensuring that your application code will successfully run in a 64-bit DB2 instance. The following steps outline how to do this:

  1. Connect to a 32-bit or 64-bit database
  2. Precompile your application using the PRECOMPILE command with the LONGERROR option specified with the value YES.
  3. For each error reported due to the use of a long data type, replace the long data type declaration with a portable host variable declaration such as sqlint32 or sqluint32. For example, replace:
    EXEC SQL BEGIN DECLARE SECTION;
    long y;        /* Generates an error on 64-bit platforms */
    EXEC SQL END DECLARE SECTION;

    With:
    EXEC SQL BEGIN DECLARE SECTION;
    sqlint32 y;    /* Acceptable to represent long on 64-bit platforms */
    EXEC SQL END DECLARE SECTION;
  4. Once all LONGERROR reported errors have been resolved, precompile your application again against the target database to create a package for the application.

Choose between building a 32-bit or 64-bit application or routine

Should you build your applications and routines as 32-bit or 64-bit objects? For all programming languages except Java and COBOL, if you know that the application or external routine will be deployed to a 64-bit platform and will be run within a 64-bit DB2 instance, you're definitely advised to compile a 64-bit version of it. If portability is an issue, then you should go the 32-bit route to simplify development and testing. If you have an extensive set of applications and routines, you might want to profile their execution time, and target the most heavily used for recompilation as native 64-bit code.

For Java applications and routines things are a bit more complex, because as of the publication date of this article, IBM has not yet released a 64-bit Java virtual machine. It is, however, possible to get 32-bit DB2 applications and routines running in 64-bit DB2 instances with IBM's Java 1.4-level 32-bit JVM. As a note, all 32-bit Java communications with DB2 databases are restricted to using Type 1 or Type 2 JDBC drivers.

As for COBOL, at this time, DB2 only supports running 32-bit COBOL applications on all supported 32-bit operating systems and on all supported 64-bit operating system platforms except Linux IA 64-bit and Linux zSeries.

In general as much as possible, try to build 32-bit applications for 32-bit DB2 instances and 64-bit applications for 64-bit DB2 instances, except where support for this does not exist. The next section addresses how to get 32-bit applications working in 64-bit DB2 instances.

Build applications so that they support cross-platform (32-bit/ 64-bit) deployment

To minimize the likelihood of application run-time problems you should build and test application code on the platform in which the application will run. If this is not practical, for example if the required compilers are not available, you can build applications on one platform and then deploy them to the platform they will run on, but special care must be taken when linking applications and when specifying run-time library paths.

To function properly, 32-bit DB2 applications must be linked to a set of 32-bit DB2 libraries and 64-bit DB2 applications must be linked to a set of 64-bit DB2 libraries. 32-bit or 64-bit specific versions of these libraries can be found in the following default directory path depending on the platform:

  • $INSTHOME/sqllib/lib (UNIX)
  • $INSTHOME\SQLLIB\lib (Windows)

where $INSTHOME is the home directory of the DB2 instance owner.

By default, in 32-bit DB2 instances this directory contains 32-bit versions of the DB2 libraries and on 64-bit DB2 instances this directory contains 64-bit versions of the same libraries. You can also access 32-bit versions of the libraries in 64-bit DB2 instances and 64-bit versions of these libraries in 32-bit DB2 instances so that you can perform cross-platform development. The 32-bit and 64-bit DB2 libraries are located in the following directories:

Table 7: Bit requirements for C and C++ data type representations in 32-bit and 64 bit applications
Platform32-bit DB2 library directory64-bit DB2 library directory
Windows$INSTHOME\SQLLIB\lib32$INSTHOME\SQLLIB\lib\Win64 (Windows IA 64)
UNIX$INSTHOME/sqllib/lib32$INSTHOME/sqllib/lib64

Note: $INSTHOME is the home directory of the DB2 instance owner.

You must link your applications to the appropriate set of DB2 libraries at compile/link time. You must also ensure the libraries that you linked to at compile/link time are available at application run time. You can rely on the default library paths being available or you can explicitly specify run time library paths to be used. The following steps outline how you should compile and link applications in various scenarios:

Procedure for building 32-bit and 64-bit applications for deployment to 32 or 64-bit DB2 instances

  1. Connect to a database
    • Connect to a 32-bit database for a 32-bit application or connect to a 64-bit database for a 64-bit application.
  2. Precompile the application to create a package for the application.
  3. Compile the application.
    • Compile the application in 32-bit mode for a 32-bit application or specify a 64-bit mode compiler option to create a 64-bit application.
  4. Link the application with the appropriate 32-bit or 64-bit DB2 libraries and optionally specify the run time library path:
    1. Link the application
      • For a 32-bit application being built in a 32-bit DB2 instance that is to be deployed to a 32-bit or 64-bit DB2 instance, link to the libraries in the default DB2 library path or explicitly specify the 32-bit DB2 library directory.
      • For a 64-bit application being built in a 64-bit DB2 instance that is to be deployed to a 64-bit DB2 instance link to the libraries in the default DB2 library path or explicitly specify the 64-bit DB2 library directory.
      • For a 64-bit application being developed on a 32-bit platform, link to the libraries in the 64-bit library directory.
    2. Specify the run time library path (optional)
      • For a DB2 application to run properly the run-time library path must include the DB2 libraries that were used to link the application otherwise the application will fail with a library load error. The run time library path can be explicitly set at link time or can be specified at application run time. If the DB2 libraries used to link the application are not in the default DB2 library directory at run time you must explicitly specify where these libraries can be found. Techniques for specifying the run time library path are detailed below.
  5. Bind the application to a database on the target server.

Specify the application run-time library path using an absolute path

When you deploy an application from a source computer to another target computer, it is important to consider what the run-time library path specification is on the target computer, since any relative paths you used when linking the library on the source computer may be invalid on the target computer. Thus it is often very useful to specify the run-time library path with an absolute path when you link the application using an additional linker option - most compilers provide support for this or just prior to running the application.

The following examples explore some options for specifying the run time library path:

Example 1: Deploying applications to similar (32-bit / 64-bit) instances on different computers

Joe built a 32-bit application using the default DB2 library path in a 32-bit DB2 instance on his DB2 development computer. Joe wants to deploy this application to a 32-bit production instance on another computer – his production computer. On the development computer, Joe's local DB2 instance name is JOEINST and his home directory is /home2/joe/. That is:

Development computer
# DB2INSTANCE = JOEINST
# HOME = /home2/joe

Joe's production computer has a DB2 instance with the same name; however, on this computer Joe's home directory is defined differently:

Production computer
# DB2INSTANCE = JOEINST
# HOME = /u/production

Joe specified a run-time library path at link time and it included the relative path value:

$HOME/sqllib/lib

When Joe tries to run this application on the production computer, it will fail to run due to a loader error, because $HOME on the production computer resolves to something different than it did in the development environment so the required run time libraries cannot be located. This illustrates why relative path specifications can be problematic.

One way of solving this problem is to not use $HOME in the runtime load path. As an alternative, Joe could have specified a run-time library path at link-time that was based on DB2's absolute install. That is, Joe could have linked the application with the following absolute run-time library path specification:

/usr/opt/db2_08_01/lib

As long as the version of DB2 in the production environment is the same as the version of DB2 in the development environment (same version and fix pack) and both the development and production platforms are 32-bit or 64-bit, this technique will work.

Example 2: Deploying applications from 32-bit to 64-bit instances on the same computer

Azuma develops applications on a 32-bit AIX DB2 instance. When he built his most recent application Azuma specified an absolute run-time library path with the following linker option:

-L$/usr/opt/db2_08_01/lib

If Azuma updates his instance to a 64-bit DB2 instance while maintaining all other directory paths and then tries to run his application, it won't run due to a loader error, because the run time library path that he linked his application with is now symbolically linked to sqllib/lib64, which contains 64-bit DB2 libraries.

To have avoided this, Azuma should have explicitly linked his application to sqllib/lib32 instead of to the default directory path sqllib/lib. If Azuma had done this, his application would not have been impacted by the changing contents of the DB2 default library directory when the instance was updated. Adding on to what we learned in Example 1, the ideal run-time library load path specification for Azuma to use is the following:

/usr/opt/db2_08_01/lib32

This type of library load path specification is a good choice if you develop 32-bit applications on a 32-bit instance and anticipate either having to upgrade to a 64-bit DB2 instance or having to deploy the application to a computer with a 64-bit DB2 installation of the same version of DB2, because the installation paths will be the same.

This type of library path specification is not a good idea if you reasonably anticipate periodically upgrading to future versions of DB2. New versions of DB2 and DB2 fix packs may have different installation paths. If the DB2 installation path changes, applications linked with run time library path specifications based on the DB2 installation path will no longer work.

Example 3: Deploying applications from 32-bit to 64-bit instances on different computers

The techniques described in Example 2 are also appropriate for the deployment of applications from one 32-bit instance to a 64-bit instance on another computer, provided that both computers have the same version of DB2 installed and are running the same operating system.

As you can see, the type of deployment that you are planning on doing can impact how you should specify the run-time library path for your application at link time. Using an absolute path to specify the run-time library path can simplify your deployment, as long as the versions of DB2 in the development and production environments are the same.

Enable 32-bit applications to run in 64-bit environments

If you upgrade your database client or server to a 64-bit platform and want to port your existing compiled and linked applications to the new DB2 instance, depending on the platform, you might have to rebuild the application in the new DB2 instance or rebind the application and override the run-time library path specification. As we discussed earlier, the reason for this is that in the 64-bit DB2 instance the default DB2 library path may not contain the run-time libraries that your application was linked with and is required to run properly.

Rebuilding and testing your applications in a test environment that mimics your production environment before deploying them is still your best bet, but if time constraints or lack of access to the original application source code are obstacles to doing this, you can use the techniques described below.

Deployment of Windows 32-bit applications to Windows IA 64-bit platforms

Luckily, Windows 32-bit client applications and routines can run on Windows IA 64-bit servers without any changes to the 64-bit environment. There is no need to rebuild to override the run-time library path to enable these applications. However, 32-bit Windows applications will not perform well on Windows IA 64 platforms, so if performance is a concern, it is highly recommended that you rebuild your applications as 64-bit Windows applications.

Deployment of UNIX 32-bit applications to UNIX 64-bit platforms

All supported 64-bit versions of DB2, except Linux IA 64-bit, Linux PPC 64-bit, and Linux zSeries support migration of existing 32-bit client applications. To migrate a 32-bit client application on these platforms, you must rebind the application and run it with the correct library path setting.

On HP-UX, you can only migrate a 32-bit client application if the application was originally linked with the +s option. If it was not originally linked with the +s option, you must rebuild the application with the +s compiler option or rebuild the application with the embedded run-time path set to include the 32-bit DB2 library (see below).

On UNIX, the correct library path for 32-bit DB2 applications in a 64-bit environment is INSTHOME/sqllib/lib32 where INSTHOME is the home directory of the Version 8 instance owner; however, if you have, or plan to have, 64-bit applications also run in the environment, you should not change the LIBPATH environment variable's default setting from INSTHOME/sqllib/lib64 to INSTHOME/sqllib/lib32, since the new setting will affect all applications (32-bit and 64-bit) in your DB2 instance.

So that your 32-bit and 64-bit applications each correctly reference the appropriate DB2 library directory, you can create a wrapper script to run your application such that the script sets the run time library path (or any other required environment variables) just before running the application and then resets it afterward. This is an example of a wrapper script you could use:

 #! /bin/sh
 echo <ENV_VAR_SETTING>
 export <ENV_VAR_SETTING>
 echo
 echo Running application...
 $1
 echo ...Done running application.

where <ENV_VAR_SETTING> is the environment variable that sets the run time library path for the specific platform.

Table 7: Bit requirements for C and C++ data type representations in 32-bit and 64 bit applications
PlatformEnvironment variable setting
AIXLIBPATH=$HOME/sqllib/lib32:$LIBPATH
LinuxLD_LIBRARY_PATH=$HOME/sqllib/lib32:$LD_LIBRARY_PATH
SolarisLIBRARY_PATH=$HOME/sqllib/lib32:$LD_LIBRARY_PATH
HP-UXSHLIB_PATH=$HOME/sqllib/lib32:$SHLIB_PATH
-OR-
LD_LIBRARY_PATH=$HOME/sqllib/lib32:$LD_LIBRARY_PATH

Note: The wrapper only works on HP-UX if the application was linked with the +s option.

You can run this wrapper program by entering the wrapper script name on the command line followed by the executable name:

	   <wrapper_script> <executable>

Changing the environment variable inside a wrapper might not work for applications that invoke other executables (such as with a C system call) if the wrapper script's library path is not compatible with the invoked executable. To migrate these applications, the object file must be re-linked, and then the application rebound.

In general, the runtime library path lib32 should be used to link the object file, rather than the environment variable for the platform. The C, C++, and CLI build scripts for the sample programs use the appropriate run-time path to allow new applications to easily port to 64-bit platforms.

The same link options should be used to link object files of existing 32-bit applications on 64-bit platforms (see the build scripts in the samples related links below). The following flags can be used to include the 32-bit db2 library in the runtime library path:

  • HP-UX: -Wl,+b$DB2PATH/lib32
  • Linux: -Wl,-rpath,$DB2PATH/lib32
  • Solaris: -R$DB2PATH/lib32
  • AIX: -L$DB2PATH/lib32

For AIX, you can alternately use the -blibpath linker option to specify a complete run-time library path.

Notes:

Each of these commands assumes that the compiler is used for the linking as opposed to linking with ld directly.

  1. On Solaris, LD_LIBRARY_PATH and LD_LIBRARY_PATH_32 must be unset before you link the application with the runtime path. If not, the LD_LIBRARY_PATH or LD_LIBRARY_PATH_32 setting will be used instead of the runtime path setting.
  2. On Linux, if you are using the --enable-new-dtags link option, unset LD_LIBRARY_PATH. before running the 32-bit executable. If you do not, the LD_LIBRARY_PATH setting will be used instead of the runtime path setting.

For more information on when and how to create a platform-specific script that can be used to set the run-time library path, refer to the following topic:
Migrating applications from 32-bit to 64-bit environments - an IBM DB2 documentation topic


Summary

DB2's support for cross-platform development enables you to flexibly develop and deploy database applications to different platforms. This article has provided you with information about DB2's 32-bit and 64-bit application and routine support and has shown you the importance of developing and building applications such that down the road should you migrate your database to a different platform it is easier to also deploy your applications.

This article should have helped you:

  • Understand the relationship between 32-bit and 64-bit objects in your computing system
  • Learn about DB2's 32-bit and 64-bit application and routine support
  • Learn how to develop applications that will be portable later on
  • Determine a strategy for referencing library paths that your applications depend on to function properly

In general you should adopt a single strategy for developing and deploying your applications so that you have fewer special case scenarios to manage which can be time consuming and frustrating. The real lesson in developing your applications for cross-platform deployment is to "Think Ahead." An ounce of planning at application development time is worth a pound of pleasure when your application deployment goes smoothly.


Acknowledgements

I would like to thank Peeter Joot, Ryan Mayor, Ronald Trueblood, and Dan Behman for providing me with information and for taking the time to review this article.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=23543
ArticleTitle=32-bit and 64-bit application development with DB2 UDB V8.2
publish-date=10072004