DB2 V12 Features Supporting Large Databases

Big data applications were once limited to hybrid hardware/software platforms. Now, recent advances are allowing applications like these to be integrated with and federated into operational systems. In particular, IBM's DB2 for z/OS Version 12 (DB2 V12) delivers new features and functions that allow the DBAs to design, define and implement very large databases and business intelligence query platforms that fulfill some big data expectations. Such applications can then provide value to the enterprise without expending money and time on specialized infrastructure, and can also be the first step towards building out one or more true big data applications.

Dynamic SQL Plan Stability

Many operational systems access DB2 through SQL statements that are hard-coded and never change. These are called static SQL statements. During the program preparation process called “bind”, these static SQL statements are read and stored in DB2 along with the optimal data access path. During execution, DB2 uses the pre-determined access path of the SQL statement. The DBA regularly reviews these stored statements and the corresponding access paths in order to do performance analysis.

Contrast this with SQL statements that arrive at DB2 in an ad hoc fashion. These SQL statements are created real-time in applications or by query tools, and are termed dynamic SQL. DB2 is forced to parse these SQL statements on arrival, determine the optimal access path and then execute the query. This leads to several issues. Repeated execution of the same SQL statement or similar statements may result in different access paths at different times, perhaps as a result of one of the following:

◉ Changes in data distribution statistics;
◉ Changes in DB2 code due to patches, upgrades, or new releases;
◉ Changes in DB2 subsystem execution parameters;
◉ Changes in hardware or software configurations, including memory allocations and CPU availability.

In order to reduce the instability of dynamic SQL access paths, DB2 V12 includes several parameters that can be set to capture and monitor dynamic SQL statements and maintain current access paths despite hardware or software configuration changes.

Dynamic SQL can now be captured, monitored, and reviewed for performance. The DBA can configure DB2 to maintain particular access paths for a set of dynamic SQL statements while allowing others’ performance to change as the system changes. This is extremely useful for applications that construct queries based on a set of user choices.

For example, consider a customer information system that retrieves a set of customer records based on criteria such as customer name, location, account type and account balance. It is possible that only some of the criteria are entered by the user, so the application may construct one of several different SQL statements. As the number of criteria increases the number of possible SQL statements increases geometrically.

With dynamic SQL plan stability, the DBA can capture the various SQL statements along with their access paths and have DB2 continue to use the same access path while making performance changes such as adding memory, adding indexes or updating data distribution statistics. This alleviates the potential problem of these changes potentially influencing DB2 to choose another access path that does not perform as well.  In short, DBAs now have the capability to measure whether their performance changes actually result in better access paths before implementing the changes in production.

DRDA Fast Load

Many applications use the LOAD utility to perform large data loads. (Single rows can easily be loaded using the SQL Insert statement.)  Some examples of data loads include loading or appending today’s transactions to a Transaction table, or loading new customers to a Customer table.  In a data warehouse environment, the LOAD utility is used extensively when extracting daily data from operational systems as part of the extract transform load (ETL) process.

For data originating in remote systems, the ETL process can be quite cumbersome and I/O-intensive. Consider a daily transaction file on a remote system. The file needs to be extracted from the originating system (copy 1), sent to the mainframe (copy 2), transformed and sorted in the proper key sequence (copy 3) then loaded into a DB2 table. The multiple copies and multiple data movements seem redundant.

The data protocol used to communicate between the remote system and mainframe DB2 is called distributed relational database architecture, or DRDA. With the new DRDA Fast Load capability, applications can invoke a remote load process using a local DB2 client application. This application will send the data directly to the mainframe for processing directly by the Load utility.

This has several advantages for the DBA to consider. It reduces mainframe CPU by placing the extraction and transformation logic on the same platform as the originating system. It also bypasses several file creation steps, reducing I/Os, disk space usage and total elapsed time.

Table Partition Limitations Removed

One of the reasons that big data appliances are so popular is that they can store so much data. While this may be obvious, prior versions of DB2 had limits on table sizes that precluded using DB2 tables for very large databases. Thus, big data applications were used to store large tables and the result was integrated into the current DB2 subsystem, usually including the enterprise data warehouse.

Prior to DB2 V12, table partitions were limited as well as the total number of partitions per tablespace. A typical table using 4k pages was limited to 16 terabytes (TB) total size.

DB2 V12 introduces a new type of partitioned table called “partition by range relative page number”. This tablespace type allows each partition to be 1 TB in size, and the total size of a tablespace is increased by a factor of over 100, to 4 petabytes (PB). In terms of rows, the largest number of rows allowed in one of these new tables is approximately 280 trillion.

This massive increase in allowed table size means that DB2 now includes native options for storing large amounts of data. This may be a viable option for shops whose limited budgets preclude leasing a big data appliance, or who wish to implement a big-data-like application in native DB2 first as a proof of concept prior to considering a big data application.

Continuous Delivery

One issue that DBAs and systems support staff have had concerns about in recent years is the complexity of DB2 version upgrades. Many mainframe shops implement multiple DB2 subsystems in a cluster configuration called data sharing. Since each DB2 subsystem is a separate software instance, each must be updated when IBM issues DB2 software updates or a new version. Some of the new version code and system databases are shared by all the subsystems, and some is unique to each subsystem. Thus, the process for upgrading them all can be complex, especially when systems support must take into account undoing the upgrade or falling back to a prior version should issues occur.

With continuous delivery, IBM has simplified their method of software delivery. Upgrades are now provided in smaller pieces that are much easier to implement and faster to test. Shops can choose which subsets of functionality they wish to apply at what time and to what subsystems.

Continue reading

Big Data Analytics on Current Data

Typical big data applications have a load phase, where a snapshot of data is extracted from operational systems, transformed and cleaned, and then loaded into your big data appliance. Analysts and data scientists can then execute business analytics (BI) queries against the data store in search of patterns and potential marketing opportunities and cost savings. One disadvantage of this architecture is that your appliance becomes more like a large data warehouse, in that it does not contain the most up-to-date data.

IBM now provides an option to configure its Db2 version 11 for z/OS and complementary IBM Db2 Analytics Accelerator (IDAA) to permit concurrent transactional processing of operational data with analytics processing of data in the appliance. This new feature, zero-latency HTAP (hybrid transactional analytical processing) provides a patented replication process that propagates native Db2 table changes to the IDAA data store. This then allows BI queries to act on up-to-date data, providing more value to the enterprise, and enabling analytics embedded in operational applications.

State of the Art

Early information technology (IT) systems and applications were fairly simple. Applications read and wrote records to and from keyed files or databases, and this held true for both internal applications (such as accounting, shipping and receiving, and various reports) and external or customer-facing ones, which included order entry, bank teller screens and information kiosks.

Over time application complexity grew along with data volumes. IT began to create data stores that were much more than simple keyed files. Daily extracts from order entry systems were sent to data marts that were analyzed to predict product shortages in stores and warehouses, which then sent data to shipping systems. As historical analysis and reporting became more important to the business, daily extracts were accumulated in a data warehouse, providing customer, account and product information that could be aggregated by region or time period. Analysts could then review these data for trends and make predictions about which products sold best in what regions or during which time periods.

Today, there are many more CPU-intensive and data-intensive operations across IT than ever before. Operational systems have grown from simple, in-house programs to include large suites of software from third-path vendors, including enterprise resource planning (ERP) packages and business intelligence (BI) systems for querying the data warehouse. Extract files from operational systems have grown much larger as analysts requested more and more data. These bulk data files became the source for loading into big data appliances, whose proprietary data formats and massively parallel data processing resulted in greatly reduced query response times.

Today’s Requirements

Now, IT is at a crossroads. The business has two new needs, and these needs are extremely difficult to satisfy simultaneously. They are as follows.

The need for analytical queries to access current data. Advances in big data query speed and the value of timely business intelligence made it essential that some analytic queries execute against today’s data. This can occur when real-time data is more important or relevant than historical data, or when the incidence of important events (and the data corresponding to them) is relatively rare. Consider the example of a BI analyst reviewing product purchases. The relevant queries may calculate the total profit realized for all products in every month for every sales region. For popular products with hundreds of orders per day, including today’s orders in the calculation may not make a significant difference; however, for products ordered only once per month per region, today’s data may almost double the totals.

The need for some operational applications to run analytical queries. This is mainly in situations where real-time data is far more valuable than historical data. For example, consider health care equipment monitoring a patient. Historical analysis may indicate the conditions under which you may be able to predict when events such as strokes or heart attacks may be likely; however, this may only be of value to a patient if the prediction is done immediately as data becomes available. Another example is a supply chain system that predicts product shortages and responds by scheduling shipments; as above, this is most valuable when analyses can make predictions based on current data. A third example is an on-line order entry system that needs to detect potential fraud. Fraud patterns may exist in the historical data stored in the warehouse or the big data appliance, and there may be a business requirement to detect fraud as early as possible, even at the point of initial order entry.

Early Issues

The first attempts to merge operational and analytical systems and data met with several problems. One overarching issue was the different performance and resource usage characteristics of the two environments. Owners of operational systems require strict service level agreements (SLAs) that include limits on maximum transaction elapsed times. To address this, DBAs tend to use indexes and data distribution schemes such as data clustering and partitioning to speed query performance. The analytic environment is quite different. Here, massive amounts of data are stored in a proprietary format that is optimized for fast analytic query operations such as aggregation and subsetting. Indexes and classic partitioning schemes are generally not available, with performance depending more upon parallel access to a large disk array.

Other significant issues included the following.

Data latency. Data extracted from operational systems took time to make its way to the warehouse or big data appliance, mostly because the extract, transform and load (ETL) processes needed to pass all data through multiple processes. For example, today’s current orders may require shipment from the system of origin to the analytical environment, passing through several transformation and cleaning jobs, before being loaded. This delay meant that queries could not access up-to-date data.

Data synchronization. Operational data extract jobs were usually executed on a file or database basis; that is, separate files were created for each of products, customers, orders, accounts and so forth. Since each file was then transformed and loaded separately, the analytic environment might contain the most recent data for only some entities, leading to inconsistencies (such as today’s orders but not today’s customers).

Infrastructure complexity. Applications across IT usually exist on multiple hardware platforms, with data in differing formats. Adding cross-system interfaces that couple the operational and analytics environment may require sophisticated hardware or connectivity techniques, special performance and tuning processes, and staff that are experienced in a host of different technologies.

Early Solutions

IT management was able to meet the two conflicting needs (queries in the analytic environment run against current data, and operational applications running analytic queries) in some limited cases. They developed methods of quickly loading selected operational data into the warehouse or big data appliance for operational access. These methods usually selected limited amounts of real-time data, bypassed transformations and used various fast load processes.

One possibility was to use data replication technology to extract current data on a transaction by transaction basis and pass it to the analytics environment for immediate publishing, rather than accumulating all transactions for the entire day for a nightly load.

The next logical step was to determine if there was a combined, or federated, solution that could logically merge the operational and analytic environments. Alternatively, was some kind of hybrid solution possible?

In early 2014, Gartner defined a new category of environment that they called “hybrid transaction / analytical processing” (HTAP). They characterized this as a new “opportunity” for IT to “... empower application leaders to innovate via greater situation awareness and improved business agility”.

This description did not specify how to create an HTAP environment. Businesses and vendors attempted several different configurations, each with their advantages and disadvantages.

A multi-site solution. This is probably the simplest, and is the most likely one to exist currently in your IT shop. Operational and on-line transaction processing are executing in one system, while analytic data is stored and queried in another. If the two systems are physically separate, then you can either develop a federated answer (where a new, central hardware/software solution will access both environments) or develop methods to transfer processing and/or data from one environment to another. For example, you can copy relevant subsets of analytic data to your operational system for applications to do analytics locally. Another method would be to develop pre-coded analytic processes that are installed in your analytics environment, then couple that with a high-speed execution and data transfer method. Stored procedures are one possibility for doing this.

A single-site solution. Merging your two differing environments into a single one may seem like a recipe for disaster; after all, they have two different performance characteristics. In addition, you wouldn’t want resource usage such as CPU cycles used for analytics queries causing performance issues with on-line transactions. Luckily, there are several ways to address this issue. IBM z-series mainframes allow the definition of multiple instances of the operating system called logical partitions (LPARs). Each LPAR can be defined with specific resources and resource caps. For example, the analytics LPAR could have a limit on the amount of CPU it could use.

A hybrid solution. This is one with special-purpose hardware and/or software coupled with dedicated disk storage and large amounts of memory. The intent is to permit sufficient specialized environments for transaction and analytical processing while at the same time defining federated access to processes and data.

Hybrid Solution from IBM

A hybrid solution was recently presented by IBM as a Technical Preview (i.e., not yet generally available). It is a combination of three products: Db2 11 for z/OS, BM Db2 Analytics Accelerator (IDAA) V5.1 for z/OS and IBM InfoSphere Change Data Capture (CDC) for z/OS V10.2.1.

Db2 11 stores operational data in a standard relational database for use by operational applications. The IDAA stores big data for analytics processing. Finally, CDC is used to replicate completed transactions from Db2 to IDAA. There are several advantages to this solution.

◉ All hardware and software are available from a single vendor, thus avoiding the issues of interfacing and maintaining multiple solutions from multiple vendors;

◉ Db2 11 and IDAA run on IBM z-series hardware that easily supports enterprise-sized applications and data;

◉ Operational and analytic workload processes will not compete for CPU, network and I/O resources, as the database and big data appliance are separately maintained.

IBM’s solution will be soon fitted to execute with the latest version of its relational database product, Db2 12 for z/OS.

Continue reading

SQL*Loader Enhancements in Oracle Database 12c

This article presents an overview of the enhancements to SQL*Loader in Oracle Database 12c Release 1 (12.1).

◉ Setup

The examples in this article require the SCOTT schema. If you are working in a multitenant environment, edit the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script, amending the connection to the SCOTT schema as necessary, then run the script when connected to the relevant PDB as a privileged user.

conn sys@pdb1 as sysdba
@?/rdbms/admin/utlsampl.sql

Create a CSV file to load later. This should be placed in a suitable NFS mount point. In this case I'm also placing it into the "/tmp" directory so as not to confuse when discussing non-NFS related functionality.

CONN scott/tiger@pdb1

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

-- Create data file.
SET PAGESIZE 0 LINESIZE 100 TRIMSPOOL ON FEEDBACK OFF
SPOOL /nfs/EMP.dat.tmp

SELECT empno || ',' || ename || ',' || job || ',' || mgr || ',' ||
       hiredate || ',' || sal || ',' || comm || ',' || deptno
FROM   emp;

SPOOL OFF
SET PAGESIZE 14 FEEDBACK ON

-- Clean up the file.
HOST cat /nfs/EMP.dat.tmp | grep '[0-9]\{4\}' > /nfs/EMP.dat
HOST cp /nfs/EMP.dat /tmp/EMP.dat

Create directory objects to allow the SCOTT user to load the data file from both locations.

CONN sys@pdb1 AS SYSDBA

-- Create a directory pointing to an NFS location.
CREATE OR REPLACE DIRECTORY nfs_dir AS '/nfs/';
GRANT READ,WRITE ON DIRECTORY nfs_dir TO scott;

-- Create a directory pointing to an non-NFS location.
CREATE OR REPLACE DIRECTORY tmp_dir AS '/tmp/';
GRANT READ,WRITE ON DIRECTORY tmp_dir TO scott;

We also need a table to load into, so we will create a copy of the EMP table called EMP2 without any data.

CONN scott/tiger@pdb1

CREATE TABLE emp2 AS
SELECT *
FROM   emp
WHERE  1=2;

◉ Direct NFS (DNFS) Support

SQL*Loader now supports the use of Direct NFS (DNFS) to improve performance when the data being loaded resides on NFS filers. DNFS is used by default for files over 1G in size. Explicit control is possible using the DNFS_ENABLE and DNFS_READBUFFERS parameters, with the DNFS_READBUFFERS parameter defaulting to 4.

Create a file called "/nfs/EMP.ctl" with the following contents.

OPTIONS (
  BADFILE '/nfs/EMP.bad'
  LOGFILE '/nfs/EMP.log'
  DNFS_ENABLE TRUE
  DNFS_READBUFFERS 10
)
LOAD DATA
INFILE '/nfs/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
   (empno,
    ename,
    job,
    mgr,
    hiredate   DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal,
    comm,
    deptno)

Run the following SQL*Loader command to load the data into the EMP table, replacing the existing data.

$ cd /nfs
$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

◉ Extended Data Type Support

When a database is configured to use Extended Data Types, SQL*Loader operations against the database will support the extended data types also.

◉ SQL*Loader Express

SQL*Loader Express is really just a simple extension of the command line interface, such that all the default values are targeted at handling basic CSV files that match the structure of a table. For example, if we want to load data into the EMP2 table, the default settings will look for a dump file called "EMP2.dat" that internally matches the structure of EMP2 table. As a result, we only need to specify command line arguments if the derived and default settings are not acceptable.

$ cd /tmp
$ cp EMP.dat EMP2.dat
$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\"
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Sep 13 22:18:46 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMP2
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table EMP2:
  14 Rows successfully loaded.

Check the log files:
  EMP2.log
  EMP2_%p.log_xt
for more information about the load.
$

◉ SQL*Loader Command Line Changes

SQL*Loader Express is the main change for the command line, but there are some additional changes.

The TRIM parameter allows you to override the default LDRTRIM functionality with one of LRTRIM, NOTRIM, LTRIM, RTRIM.

$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\" \
  TRIM=LRTRIM

The DEGREE_OF_PARALLELISM parameter can be set to an integer value, DEFAULT, AUTO or NONE, with AUTO being the default value.

$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\" \
  DEGREE_OF_PARALLELISM=4

The BAD, DISCARD, and LOG parameters can be specified using just a directory object name.

$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\" \
  BAD=TMP_DIR DISCARD=TMP_DIR LOG=TMP_DIR

◉ SQL*Loader Control File Changes

Not surprisingly, most of the SQL*Loader control file changes are similar to those introduced for ORACLE_LOADER access driver for external tables.

The INFILE clause now accepts wildcards, where "*" matches multiple characters, while a "?" matches a single character.

INFILE '/nfs/EMP*.dat'

INFILE '/nfs/EMP?.dat'

Files using CSV (comma-separated-values) format can use the simplified FIELDS CSV clause. The
default settings for this can be modified as described here.

LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS CSV WITH EMBEDDED
   (empno,
    ename,
    job,
    mgr,
    hiredate    DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal,
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

A default datetime format can be specified for all datetime fields using the DATE FORMAT clause.

LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
DATE FORMAT "DD-MON-YYYY HH24:MI:SS"
   (empno,
    ename,
    job,
    mgr,
    hiredate DATE,
    sal,
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

A default NULLIF can be specified that applies to all character fields. A field-specific NULLIF overrides the default NULLIF. The NO NULLIF clause can be used against a field to prevent the default NULLIF applying to it.

LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
NULLIF = "NONE"
DATE FORMAT "DD-MON-YYYY HH24:MI:SS"
   (empno,
    ename,
    job,
    mgr NO NULLIF,
    hiredate    DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal NULLIF job="PRESIDENT",
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

The FIELD NAMES clause allows you to specify the order of the fields in the data file, saying they are either in the first line of the file, or in a separate file.

◉ Audit SQL*Loader Direct Path Loads

Oracle 12c allows SQL*Loader direct path loads to be audited by creating an audit policy.

CREATE AUDIT POLICY policy_name
  ACTIONS COMPONENT=DIRECT_LOAD [LOAD | ALL];

I'm having some trouble getting this auditing to work using the "ALL" option. Thanks to Ronan for pointing out the "LOAD" method was working as expected.

When this policy is applied to a user, their SQL*Loader direct path operations will appear in the audit trail. The following policy audits all SQL*Loader operations. The policy is applied to the SCOTT user.

CONN sys@pdb1 AS SYSDBA
CREATE AUDIT POLICY audit_sl_load_policy ACTIONS COMPONENT=DIRECT_LOAD LOAD;
AUDIT POLICY audit_sl_load_policy BY scott;

Use the following control file to run SQL*Loader.

OPTIONS (DIRECT=TRUE)
LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
   (empno,
    ename,
    job,
    mgr,
    hiredate   DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal,
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

Checking the audit trail shows the SQL*Loader run was audited.

CONN sys@pdb1 AS SYSDBA

-- Flush audit information to disk.
EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A30
COLUMN action_name FORMAT A20

SELECT event_timestamp,
       object_schema,
       object_name,
       action_name,
       direct_path_num_columns_loaded
FROM   unified_audit_trail
WHERE  audit_type = 'Direct path API'
ORDER BY 1;

EVENT_TIMESTAMP   OBJECT_SCH OBJECT_NAME  ACTION_NAME          DIRECT_PATH_NUM_COLUMNS_LOADED
------------------------------      ----------    ------------------------- -------------------- ------------------------
19-OCT-2015 07:50:08.335507    SCOTT      EMP2                           LOAD                     8

2 rows selected.

SQL>

Continue reading

Database Migration - It's More than Running exp and imp

The new server using Red Hat is up and running, Oracle RDBMS software has been installed, a new instance is running with all of the new features you can possibly imagine (at long last, run_fast=true is available), and "all" that's left to do is migrate the source database (or schema) to the new/target database. It is midnight Friday and you have an 8-hour planned outage/maintenance period available to perform the migration. What can you do prior to midnight Friday to make the migration as painless as possible? Let's look at four areas where planning can make a difference: general administration, the export phase, the import phase, and a handoff phase.

General administration/project planning

You are the one in charge of the database migration. What do you and Hannibal from the A-Team have in common? Answer: "I love it when a plan comes together." To help make the plan come together, fire up Visio or PowerPoint and diagram a workflow process. As a minimum, you can take the low-tech route and come up with a timeline. Even if you start with brainstorming and writing down ideas as they come to mind, you will be much better off having everyone on the same sheet of music (or, "One band, one sound"). Items to consider include:

◉ Diagramming the workflow/process, coordination meetings

◉ Assign responsibilities among team members, establish roles and responsibilities

◉ Create and distribute a contact list, include how to get in touch with other key personnel (managers, system administrators, testing, developers, third party/application providers, customers, account managers, etc.)

◉ Hours of operation for Starbucks (some of them open an hour later on Saturdays)

◉ After hours building access for contractors (meet at a designated place and time?)

◉ Janitorial services – do they alarm the building/office when they are done? There is nothing like an alarm going off, as you walk down the hall, to add a little excitement to the evening.

◉ Notification to security/police regarding after hours presence ("Really Officer, we work here, we're not just sitting here looking like we work here")

◉ Establishing a transfer point on the file system and ensuring there is enough disk space for the export

◉ Acquiring a complete understanding of schema changes (how and when key tables get altered/modified, to include data transformation processes)

◉ Establish a work schedule (does every DBA need to be present the entire time, or can schedules be staggered?)

Pre-export and export phase

Aside from a shortage of time, there is very little to prevent you (or the person in charge of export) from practicing the export several times over and ensuring there are no glitches in this part of the plan. Does the export have to be a one-step/export everything from A to Z process? How about phasing the export by functional groups? Consider breaking up the export into functional groups: support tables, main tables, altered tables, and historical/static tables.

By grouping tables in this manner, you can interleave export and import. Once the export of a group is complete, you can start its corresponding import. It may take two hours to export and four hours to import, but that does not mean it takes six consecutive hours. Why is there a time difference between export and import? Export and import are not one to one. Export will run quite a bit faster than import, and both can run faster if optimized a bit. Do not forget that indexes are not being exported. Indexes will be re-built after the data is loaded in the target database.

How are you driving the exports: interactive mode or use of shell scripts and parameter files? Shell scripts should have four key features:

◉ An interview process

◉ Feedback or a summary of what was entered

◉ Existence checks (includes parameter files, ability to write to the dump and log file locations, and database connectivity)

◉ Bail out mechanisms ("Do you want to continue?") after key steps or operations

One script can drive the entire export process, and the bail out points can be used as signals (accompanied by extensive use of echo statements which denote where you are in the process). A key metric to be determined while practicing and refining the scripts is that of the time it takes to perform all exports.

If a schema migration is taking place (as opposed to a full database migration), what are the dependencies among schemas? Look for names/items such as build_manager, process_logger, and stage (more germane to a warehouse). "Build_manager" (as an example of a name) may contain common or public functions, procedures and packages. Process_logger may be the owner of process logs for all schemas (fairly common if you see "pragma autonomous_transaction" in the text of a source; it is a way of capturing errors during failed transactions). Unless the new schema incorporates these external or associated schemas, some or all of these otherwise "Left Behind" schemas need to be accounted for in the target database.

While the export is taking place, what is happening with the non-exported schemas? You may need to disable connections, change passwords, disable other processes, and suspend crons while the export is taking place. Web applications connections tend to be like crabgrass (i.e., hard to kill), and an effective way of stopping them is to change a password. Finally, what is the disposition of the source database, that is, assuming your plan comes together?

Import phase

Practice creating schemas and associated physical/logical objects such as tablespaces and datafiles. End result desired here is no ORA-xxxxx errors whatsoever, and all create scripts should be re-runnable. With respect to import parameter files, ensure fromuser marries up to touser. Using what was gleaned from the indexfile, pre-create tables in the target database.

For tables undergoing a modification, questions to ask include where, when and how does that take place? Do the changes occur within the user's schema, or within a temporary or migration schema, followed by "insert into new version of table as select from temp table?"

Fully understand how major tables are being changed – you may take for granted what appear to be ash and trash "not null" constraints, but application changes may completely rely upon them. In other words, it may not be enough to take care of PK, FK and unique constraints when trying to rebuild a table on the fly because there was some hiccup in the process.

What about cron and database jobs? How are you migrating/exporting all of those? Something which frequently goes hand in hand with cron jobs is email. Is the new server configured for email notification? Are there any database links to create?

Do you need logging turned on while the import is taking place? Is it even necessary to log everything being imported? What about triggers, especially the "for each row" kind? Millions of rows inserted via import equals millions of times one or more triggers fired on a table with that kind of trigger. If the trigger on a table back on the source database already took care of formatting a name, does it need to be fired again during an import?

You can be clever and disable quite a few automatic functions to help speed up the import, but don't be too clever by half, that is, do not forget to re-enable whatever it is you disabled. At 5:30 in the morning, having worked all day Friday (in addition to coming back at 11 to get ready for the midnight starting gun), sleep deprivation can introduce a significant amount of human error. If you have to go off your game plan, have someone double check your work or steps, especially if the object being manipulated is of key importance to a database or schema.

Post import considerations

Did everything work? Breathe a sigh of relief, but the job is not finished. What are you using for a baseline backup, once everything is up and running after the migration? Are you transitioning from export/cold/hot backups to RMAN? Has RMAN backup and recovery been practiced yet?

Plan A, obviously, is a success from start to finish. However, despite all best intentions and planning, what is Plan B? What if, for some undeterminable reason, applications fail to work properly after the migration? Thorough testing minimizes this, but what if no large scale testing took place? What does it take to revert to the source database? Do you have the time to try again? A second attempt will not take as long assuming you trust what took place in the export(s).

Do not assume everyone knows or understands what just took place. For example, do customer support personnel know how to point desktop CRM applications to the new database? Or are they opening trouble tickets a few weeks after the fact to complain how their changes are not being made or are not taking effect? What may be blindingly obvious to you as a DBA may be completely obscure to people who don't speak "database."

In Closing

The tips and steps covered in this article are based on real events, places, and persons. I have personally witnessed the customer service rep complaining about how his changes were not showing up, and it was because he had no idea whatsoever about pointing his desktop CRM application to the new database. Was that the DBA's or his manager's responsibility to propagate that information to him? I have seen key tables have problems with the insertion of transformed data and workarounds such as "create table as select" from the stage or transformation table implemented, but alas, the stage table did not have all of the not null constraints as did the new "real" table, and there goes the Web application down the drain.

The sad truism about a database migration is that if you do not have the time to test beforehand and wind up failing (the reason why is immaterial), it is amazing how time magically appears to perform testing before the second attempt. The tips mentioned in this article should give you a good perspective regarding some the external factors which come into play during a migration.

Continue reading

Db2 Application Performance Enhancements

Db2 Version 12, the latest version of IBM's flagship database management system for the z/OS hardware platform, includes many new and improved features specifically aimed at improving application performance. As today's development teams are driven to implement applications at a faster pace, the DBMS must support the ability to retrieve data quickly, while at the same time reducing overall resource usage. Here is a detailed look at some modern transactional data processing issues and how Db2 meets these challenges.

The Challenge of the Data Archive

Due to governmental regulations or compliance reasons, the information technology (IT) enterprise is required to retain much of its transactional, financial and customer-related data for long periods of time. One typical requirement: financial data must be kept for a period of seven years. The data must be retained, and must also be accessible in case of legal or audit requirements.

This places capacity planners and performance tuners in a bind, because keeping this data available in operational systems increases database sizes. This in turn leads to longer query times, larger indexes that take longer to process and increased elapsed times for utilities such as database reorganizations and database backups. IT commonly prefers to purge or archive stale or old data, keeping the sizes of operational tables and indexes manageable and permitting faster application processing. However, with the requirement to retain data, how can the enterprise meet the needs of both regulations and customer expectations?

One best practice is the data archive table. This is a table defined similarly to a base table and used to store purged or archived data. Operational systems access the base table as part of normal batch and on-line processing, while the archive table remains ready for access should the need arise. However, a problem arises when the archived data is to be queried. Since the data in both tables changes over time, how can you construct an SQL query that accesses (say) data within a range of dates, without knowing in which table(s) the data may reside? Enter the Union All operator. This SQL construct logically merges the contents of two or more tables. A simple form of the syntax is as follows:

     SELECT <column-list>
     FROM  Customer-Table
     WHERE <selection-criteria>
     UNION ALL
     SELECT <column-list>
     FROM  Customer-Table-Archive
     WHERE <selection-criteria>
     . . .
     ORDER BY <Sort-Criteria>
     FETCH FIRST n ROWS . . .

n rows to the requestor.

Db2 Version 12 provides some performance enhancements that can speed up such queries. One feature is to push the Order By and Fetch First clauses ‘down’ into each of the two subqueries. In the case of Order By, this cuts one big sort into two smaller sorts, requiring fewer resources. Also, if Fetch First is specified, then n rows can be retrieved from each of the tables (after sorting) and then merged to form a set from which the final n rows is chosen. By pushing the sort operation down into each of the subqueries, Db2 can take advantage of performance features for that table that favor the sort, such as indexes, data clustering or partitioning.

Advances in Data Compression

Data compression has been available in Db2 for several decades, beginning with Version 3. Db2 uses the common algorithm called Lempel-Ziv to provide dictionary-based, lossless compression and decompression for table data. (Variants of this algorithm are used for .GIF and .TIFF files, for .PDF files in Adobe Acrobat, and various Unix platform processes such as ARC and PKZIP.) Depending upon column data types, compression ratios of 50% - 80% are possible.

Of course, if you are storing already compressed data such as video, images or audio clips, these data are usually already compressed. Typically, such data are stored in Large Object columns (LOBs) in bit form. Processing them takes place in the application, not in the database manager. In the late 1980s a glut of new data formats for non-traditional data types appeared, including bit files from equipment monitoring, web click streams, telephone audio response clips and others related to a sudden increase in new hardware types.

Initially defined in 1998, extensible markup language (XML) provided a method for encoding data that was self-describing, and both machine- and human-readable. Although at the beginning the focus was on encoding documents, XML became popular for encoding many kinds of data structures, including web-based data. (For more information, see Reference 1.) Today, more and more data stored in relational databases is formatted with XML data types, and can be stored conveniently in LOBs. Since XML takes the form of a string of characters, data compression now becomes feasible. In addition, other types of character data are commonly stored in LOBs, including HTML (for web pages), and text files from distributed sources such as .txt files.

Db2 Version 12 provides a new ability to compress LOBs. This is different from the ability to specify data compression for an entire table, and only applies to the LOB column(s) desired. This feature uses the z/OS hardware compression feature called the zEnterprise Data Compression (zEDC) card, which uses a different compression algorithm than Lempel-Ziv. In order to take advantage of this feature there are several hardware prerequisites. Consult the Db2 performance manual for details.

Depending upon the data in the LOB, compression ratios can vary from 50 percent to as high as 90 percent or greater. Also, since the compression is hardware-assisted, very few CPU cycles are consumed during compress or decompress, leading to increases in Insert and Select throughput. DBAs can use the DSN1COMP stand-alone utility to estimate the amount of compression prior to implementing it. Note that while the data may be compressed inside the database, when it is externalized it is automatically decompressed. This requires the DBA and application developers to carefully estimate disk dataset sizes, especially for processes that unload a large number of rows.

Index Processing Gets Faster

Indexes have an internal data structure that is ‘tree-like’. This means that the first index block, or root page, contains pointers to subsequent blocks called non-leaf pages. Each of these contains pointers to lower level non-leaf pages. This continues downward until you reach the final pages, called  leaf pages, that contain pointers to the actual table data.

Some queries that access multiple table rows may use an index to retrieve data. Due to the index structure, this may require multiple scans and searches of the index. Each such search requires that the relevant index root page, non-leaf pages, and leaf pages be read into memory. Multiple scans may mean multiple accesses of these pages, and may require multiple I/Os to ensure that they remain in memory. It is also possible that queries such as these will compete for resources with queries that insert, update or delete rows. Since these other queries change the index tree structure and/or the pointers on the various pages, our sample query may have to wait until updating queries complete.

Db2 Version 12 introduces a new process called Index Fast Traversal (IFT). This uses a new memory area called Index Control that is managed by the database manager, and contains high-performance versions of index pages. When a suitable query arrives from an application, Db2 analyzes it to determine if there are potential cost savings (CPU, I/O and elapsed time) if IFT is used. If so, Db2 assembles the relevant index pages in index control, and directs query processing to this new area.

Index Fast Traversal can provide up to 20 percent CPU reduction over classic index usage. In its initial implementation, IFT is limited to unique indexes with a maximum 64 byte key length.

Continue reading

Data Masking in the World of GDPR

Your production database environment stores personally identifiable information in an encrypted form, limits access to these fields and masks them when displayed. However, some types of data encryption create potential performance problems as well as security issues. Your data may be at risk unless you mask it effectively.

The State of Database Security

The European Union’s General Data Protection Regulation (GDPR) became effective worldwide in May 2018. It applies to any company that obtains data from any customer in the European Union and provides for extremely harsh penalties for companies that breach this new law. To comply with it, companies throughout the world increased their data security awareness, appointed data protection officers and updated their privacy policies. Because GDPR specifically prohibits companies from using sensitive personal data to make business decisions, IT must identify these elements in databases and ensure that decisions such as credit checks or customer purchases are not based on these values.

Most DBAs have ensured that any such personal data retained on disk storage is stored in files that are highly secured. Unfortunately, this is not enough. In particular, non-production environments are sometimes populated with production data, and these environments do not typically implement full production-level security. In addition, developers might sometimes have "public" access that allows them to see the values of all data elements in the database.

Out of this concern arose a key data security process called data masking, which involves obscuring, anonymizing or otherwise encrypting data elements in-place. While this adds a level of security it also has performance implications, especially in a database environment. For data in simple flat files it is usually feasible to partly or fully mask data items without issue. However, in a database environment such data changes may cause issues.

All database management systems contain features that support various categories of data integrity:

◉ Entity integrity, including uniqueness and enforcing non-null values;
◉ Domain integrity, which forbids certain invalid data values such as a date of 99/99/9999;
◉ Referential integrity that defines primary and foreign keys and enforces data value relationships between tables.

In order to support these integrity rules, the DBMS includes various features such as unique and non-unique indexes, strict data typing for data elements (such as INTEGER and DATE), as well as various data column constraints. At times these features are at odds with the need to secure individual data items. How, for example, can you enforce domain integrity on a column if you encrypt the data?

Database Data Masking Concepts

There are multiple ways to mask data in order to be compliant with the GDPR. Each has advantages and disadvantages in performance and ease of implementation.

Data obscuring. Using the data obscuring method, the DBA and application developers work together to ensure that data elements are not stored or displayed in their entirety; rather, portions of the data elements are overlaid with masking characters. For example, and account number might be stored as XXXX-XXXX-XXXX-1234, where the X character overlays the initial digits of the number.

Data obscuring is easy for the application to implement when displaying or reporting such an element; however, there are problems with storing the number in this form. Multiple different account numbers that end in the same four digits will now seem equal, leading to duplication. Lookups on this number will return multiple records. It will also be impossible for the database to enforce uniqueness on this account number, and any data validation such as computation of check digits will not work. Problems such as these make data obscuring a poor choice for database storage.

Data anonymization. Using the data anonymization masking method, data elements are altered using a function that hashes or encrypts them prior to database storage. The encryption method must be reversible in order to retrieve the correct records. It is possible to implement anonymizing algorithms that preserve the uniqueness of the data element; however, it is extremely resource-intensive for data access that retrieves multiple elements and must sort them. For example, consider displaying all records for customers with Last Name of SMITH, sorted by First Name. The application must retrieve all records, decrypt the last name field, temporarily store all those with SMITH as Last Name, decrypt all the First Name values, sort the result, then finally display them in order.

This problem extends to the implementation of database indexes. Many times the DBA will create an index on a data element in order to increase SQL Select performance. However, if the data element is stored in encrypted form, such indexes may not provide any advantage.

Another issue with anonymization involves the relationships between data elements. Consider the elements in U.S. addresses of City, State and Zip-Code. These data elements are valid as a group because certain cities will not exist in some states, and every state occurs only with certain zip codes. If any of these elements is anonymized, the combined address will be invalid.

Anonymization provides a measure of security but results in too many performance and data validation disadvantages to be used on its own.

Masking tables. Using the masking tables method, IT implements a set of tables that contain valid-looking but otherwise harmless data element values. The masking algorithm first uses a hashing technique to convert the original data to an integer hash value and then uses the result as the key for lookup in the target table. For example, a Last Name of SMITH might hash to the value 123. The algorithm then references the 123rd row of the Name Masking Table and uses the name found therein.

The advantages of masking tables are many. These tables can be populated with unique values, thus allowing the results of the masking to be unique. If the masking and algorithm are properly secured, the final data cannot be decrypted by hackers. Finally, combinations of related fields (such as City, State and Zip-Code) can be hashed as a group, and the result used to retrieve a set of values in one row of a City-State-Zip masking table, thus preserving the validity of the data relationship.

Special Data Elements

Some data elements cannot be easily encrypted or masked due to their nature. Some of these are the following.

Bank account numbers. These data elements are typically stored in table columns with data types that make it tricky to validate. For many banks, not all account number combinations are valid. This is particularly true after a merger or acquisition, where there are two sets of valid account numbers, perhaps of differing lengths. Account numbers sometimes have a check digit, and some account numbers are associated with bank routing numbers. Masking these numbers will require a creative masking table approach, perhaps involving converting the combination of routing number and account number to results acceptable to applications.

E-mail addresses. Masking table entries for e-mail addresses should contain valid-looking addresses. However, it is possible that some applications may actually test validity by attempting to send an e-mail to the address. To prevent such issues, use the domain “example” in your masking table, as example.com, example.net, example.org and example.edu are all second-level domain names reserved for documentation purposes by the Internet Corporation for Assigned Names and Numbers (ICANN).

Social security numbers. There are only a few applications that test the validity of these numbers. Although the numbers are no longer tied to specific geographic locations, there are some digit combinations that are invalid. For example, in the three-group format “XXX-XX-XXXX” no single group can be all zeroes. Rather than implement as a masking table, it is faster to simply hash these numbers to a single nine-digit number and then check for validity.

Credit card numbers. These numbers contain substrings of digits that have several internal meanings. The first few digits (typically six) correspond to the issuer of the card, also called the Issuer Identification Number or IIN, and the last digit is typically a check digit calculated with the Luhn algorithm. The easiest masking method is to hash the number to a sixteen-digit result (or whatever length is required), replace the IIN with a valid value, then calculate the check digit.

Dates. Due to the way that applications handle dates, masking them is not easy. Certain dates such as BirthDate may require specialized encryption, as simply replacing them with a masked date will throw off calculations of Age, Years of Vesting, or Years Until Retirement. If masking is required, one simple way is to hash the entire date to a number in the range of one to 365 and then add the result to the date 01/01/nnnn, where “nnnn” is the original year.

Unstructured data. This includes text fields such as Remarks or Comments, where a user may deliberately or inadvertently enter a personal data element such as BirthDate. It also includes relatively new data element types such as still images (e.g. GIF, JPG or PDF documents) and data encoded as extensible markup language (XML). It is also possible, usually in a poorly-designed system, to have file names that contain this information (e.g. “Server01 / Customers / Smith / 2001-01-01”). For these data types that may contain hidden personal data you must combine good application and database design and constant monitoring to prevent security breaches.

Implementing Data Masking

In a database environment it is important to implement encryption and masking tables efficiently. The DBA should create masking tables as database tables; however, there are several options for the masking code. Some of these are:

Column constraints exist as part of the table column definition and can be used for simple data validation; however, constraints are limited to simple logic and any changes require a database change and corresponding outages for all applications.

In-application code is possible for columns that appear in a single table and are used by only a single application. However, any expansion of the columns across tables, changes to the column data type or length, or changes to the masking logic will require an application coding change, testing, and perhaps re-masking of the entire table, as well as an application outage.

Stored procedures are a possible choice for masking logic. Disadvantages include a CPU and elapsed time penalty for invoking them, as well as a small amount of added logic in the application for invocation and return code checking.

User-defined functions (UDFs) are the best method of implementing data masking logic. They provide a simple application coding interface combined with the ability to code them in the SQL/PL language. It is also possible that UDFs that are called through the distributed data facility (DDF) may be “zIIP-eligible”; that is, they may be able to use the System z Integrated Information Processor (zIIP) CPUs engines. These special-purpose CPUs can be installed on your z hardware and have the advantage of their work not being counted in most software license charges, which are commonly based on CPU usage.

Masking Next Steps

It is not enough to implement a set of masking tables and algorithms once and then forget about it. Several factors will require that you revisit your masking strategy from time to time. These factors include table column data type or length changes, migration of columns to additional tables and additions to column values.

Another issue is the status of your non-production environments. How do you create test data?

There are several ways to populate test database environments, including the following:

Masked test environment. Copy a set of data from production to a pre-production environment.  This requires enough data storage in pre-production to store an (almost) complete set of production data. Whenever test data is required, extract the needed data from pre-production and execute masking as you store the data in test. This requires CPU resources whenever you extract and mask your test data.

Masked pre-production. Copy a set of data from production to a pre-production environment and then execute masking there. You may now create test data by selecting what you need from pre-production. This requires executing masking only once.

Continue reading