I've been doing large volume data transfer (from Db2 z/OS to distributed systems) for quite some time now, and tried several solutions, including Db2 UNLOAD + FTP, Db2 (LUW) command line, and several client-side programmatoric solutions.
I found the best setup (both in terms of maintainability/readability, and end-to-end performance) to be the following:
Perl DBI (see https://dbi.perl.org/) with a Perl script in the following lines:
(this is our production script, with login info masked)
Perl has decent CSV and Excel writers, if needed, but this script just writes out comma-separated values (assuming no commas in the returned fields).
Also, the query is just plain SELECT * FROM table, which you can of course tweak to your taste.
#! /usr/bin/perl -w
use DBI;
# Table list; second arg is the "order by"
my %tbls = (
'P' => 'tutpersons plname,pfname,pa_cono',
'CI' => 'tutcompanies coname,cotown,costreetname',
# etc.
);
# database credentials
my $db2dbname = 'DB2P';
my $db2user = '********';
my $db2pwd = '********';
my $db2schema = 'TBACCAP';
my $db2 = DBI->connect( "dbi:DB2:$db2dbname", $db2user, $db2pwd)
or die 'Cannot connect to Db2 ' . $DBI::errstr;
my $stmt = $db2->prepare("SET CURRENT SCHEMA='$db2schema'"); $stmt->execute();
foreach my $abbr (sort keys %tbls) {
my @tblinfo = split ' ',$tbls{$abbr};
my $tblname = $tblinfo[0];
my $colname = $tblinfo[1]; $colname=1 unless ($colname);
my $query = "SELECT * FROM $tblname ORDER BY $colname";
$stmt = $db2->prepare($query);
$stmt->execute();
my $firstrow=1; # true
while (my $list = $stmt->fetchrow_hashref()) {
print join ',', sort keys %$list if ($firstrow); print "\n";
print join ',', map { $list->{$_} } sort keys %$list; print "\n";
$firstrow = ''; # false
}
}
------------------------------
Peter Vanroose
ABIS Training & Consulting
https://www.abis.be/html/enDB2Calendar.html------------------------------
Original Message:
Sent: Jan 28, 2023 07:55 AM
From: Steven Lamb
Subject: DB2 z/OS query tools for large result sets
We often get asked by the business to run ad-hoc queries to extract data for incidents / problems / whatever, where the result set can be hundreds of thousands or even millions of rows. The tools we have at our disposal are basically mainframe programs such as DSNTEP*, a home-grown Rexx extract program or Data Studio.
The requestors usually want the data in an Excel spreadsheet, which is a bit of a pain;
- We don't allow direct connections from Excel itself to any systems containing customer data.
- Data Studio is limited to exporting something like 65,000 rows for an "old" version of Excel, or slightly more for CSV.
- For large amounts of data, if it's done on the mainframe we have to FTP it somewhere in a CSV format and then fiddle with it.
What other tools are available for this type of task?
Regards,
Steve
------------------------------
Steven Lamb
------------------------------