DB2 - L

 View Only
Expand all | Collapse all

DB2 z/OS query tools for large result sets

  • 1.  DB2 z/OS query tools for large result sets

    Posted Jan 28, 2023 07:55 AM
    Edited by Steven Lamb Jan 28, 2023 07:58 AM
    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?


    Steven Lamb

  • 2.  RE: DB2 z/OS query tools for large result sets

    Posted Jan 28, 2023 09:56 AM
    Well, I use DSNTIAUL or a windows client for large unloads. I write SQL that generates a comma delimited file. Basically, using functions to convert everything that isn't character to character and concatenating them all together with commas. Once you get that SQL written everything is easy from that point.

  • 3.  RE: DB2 z/OS query tools for large result sets

    Posted Jan 28, 2023 10:55 AM

    Some of the queries are reasonably complex e.g. RIGHT OUTER JOINs or CTEs, so would DSNTIAUL will cope with them (I haven't used it for ages)?


    What do you mean by "windows client" in this case? As I said, we don't allow direct ODBC connections (e.g. Excel) to systems containing real data, so that limits what we can do.

    The only PC-based tool that we have at the moment is Data Studio, where only we have it installed, plus the access to the data via RACF. It would be nice if we could have a different tool that could handle larger amounts of data.


    I'm currently running stuff today via our Rexx program, but I have to remember to put the commas after every column ',',


    Then there's working out a suitable LRECL – too small and it falls in a heap; too large and the FTPs take longer and use more space. Then I had to work out why the SQL I was being provided by the applications team wouldn't run; turns out when they were pasting it into Teams, "spaces" were being transcribed as x'41', but it all looked OK.






    The contents of this email are intended exclusively for the addressee. If you are not the addressee you must not read, use or disclose the email contents; you should notify us immediately [by clicking 'Reply'] and delete this email. Nationwide monitors emails to ensure its systems operate effectively and to minimise the risk of viruses. Whilst it has taken reasonable steps to scan this email, it does not accept liability for any virus that may be contained in it. Nationwide Building Society is authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority under registration number 106078. Nationwide Building Society, registration no. 355B. Head Office: Nationwide House, Pipers Way, Swindon, Wiltshire SN38 1NW.

  • 4.  RE: DB2 z/OS query tools for large result sets

    Posted Jan 28, 2023 04:12 PM
    We wrote our own tool using opensource .NET CSV libraries and Progress .NET DB2 Provider.

    A simple app that you drop SQL into and it assumes all columns in the returned datatable become delimited output. Usually, pipe delimited but it can generate comma and data enclosed by quotes.

    CSV can be opened by excel so no need to create excel specific formats.

  • 5.  RE: DB2 z/OS query tools for large result sets

    Posted Jan 29, 2023 11:40 AM
    If you have HPU, you can specify FORMAT DELIMITED.  For complex queries, HPU will go through Db2, which you are already doing.

    Stefan Kolev SS&C

  • 6.  RE: DB2 z/OS query tools for large result sets

    Posted Jan 29, 2023 07:07 PM
    This is a 'pull' FTP process

    James Campbell

  • 7.  RE: DB2 z/OS query tools for large result sets

    Posted Jan 30, 2023 04:02 AM

    BMC AMI Unload does the trick admirably. Read all about it here: How BMC AMI Unload works - Documentation for BMC AMI Unload for Db2 12.1 - BMC Documentation



    Marcus Davage CEng CITP MBCS

    Lead Product Developer

    Intelligent Z Optimization and Transformation

    BMC Software Ltd

    Cardiff, Wales, UK


    +44 118 921 8517


    +44 7840 023 560




  • 8.  RE: DB2 z/OS query tools for large result sets

    Posted Jan 30, 2023 08:42 AM
    We do similar work with a tool called AQT (Advanced Query Tool).  Don't think you would want to use it for 'millions of rows'.  The tool is user friendly, support is excellent.  Can create scripts to run the SQL and load into Excel.

    SamBaughMotion Industries

  • 9.  RE: DB2 z/OS query tools for large result sets

    Posted Feb 01, 2023 04:54 PM



    This topic on large result sets and how to effectively pass them around is a topic of interest to me.  I may be a bit late to this conversation, but I want to add my two bits.


    When I hear about a requirement to produce a large SQL SELECT result set in comma delimited format I always ask questions about why? and the REAL original requirements.  Do they really want a million line file of comma delimited data?  What is the point?  Do they think they are going to put it into EXCEL?  EXCEL is not fun with million line spreadsheets.  Do they want to import into another database?   Understanding the requirements is important to maybe suggest alternatives.  I often find that after discussing the requirements we can find other alternatives compared to producing huge SQL SELECT results


    Assuming they really want the SQL SELECT result in comma delimted format.  Then I agree with DanL.  Use functions in the SQL SELECT to format the data and concatenate it all together so the output result set is "pretty" and "easy to read" (ie. Convert DECIMAL colums to readable char-digits.  Concanate all the data together.  Convert varchars to char, etc). 

    And then use DSNTIAUL in batch and at the end, FTP the result set to the target computer.  Of course, the pain is preparing the SELECT with all the functions to make the result pretty.  But it will make the receiver happy


    Stefan Kolev mentioned that HPU (and other similar unload utilities) have the option to accept SQL SELECT and produce comma delimited output.  Much easier then DSNTIAUL and fussing with the SQL SELECT.

    The native/default IBM UNLOAD utility does not allow SQL SELECT as input.  But you can specify a table (or full image copy) and UNLOAD with a WHEN clause (not to be confused with WHERE clause) and UNLOAD can produce comma delimited output.


    Sam Baugh mentioned AQT (advanced Query Tool).  I use this tool and I love it.  I call it a "database exploration tool" and it similar to DataStudio (and DBVisualizer and Toad and others).  AQT is relatively cheap and AQT has lots of bonus functionality compared to Data Studio and the other similar tools.  One of the cool things is that AQT can execute a SQL SELECT and send the output straight to comma delimited output file or excel format output).  This AQT function puts it straight into the specified file so you never actually see it in the tool or any window.  It works smoothly.  In Contrast, Data Studio requires me to materialize the whole result set in a Data Studio window and then I right-click and save the result set somewhere.  If the result set is large then data studio gets bogged down bringing it into the data studio and displaying it to you (even though you don't really want to look at it)






    Brian Laube Manulife Financial

    Db2 Z DBA (mostly)

  • 10.  RE: DB2 z/OS query tools for large result sets

    Posted Feb 02, 2023 03:25 AM
    As Sam Baugh said, AQT is an excellent tool



  • 11.  RE: DB2 z/OS query tools for large result sets

    Posted Feb 02, 2023 03:28 AM
    Of course I did mean Brian Laube


  • 12.  RE: DB2 z/OS query tools for large result sets

    Posted Feb 02, 2023 04:29 AM

    Mainframe queries:

    We do all the basic stuff like converting VARG to CHAR, VARCHAR to HEX etc to get the data into a pretty format; that's straightforward and isn't the issue (until you realise you've missed one column and have to start again :( ).

    We're never talking a Select from a single table, so that rules out a lot of the IBM-supplied stuff straightaway.
    We have our own Rexx exec where you just plug in the SQL, it extracts the data and doesn't bother with page headers and rubbish like that. We're "quite" happy with it - it works.

    My "complaint" is that it is a multi-step manual process to get that data out in this way and then on to my PC or a shared drive somewhere; Data Studio (when it can work) is much easier for this. Its only real limitation is the size of the result set it can export.

    As to why the support team need so much data is a different question altogether. We don't know how they actually use the data; I'm not saying they're always right when they ask for stuff. Having it in Excel formatted as a table or whatever allows you to perform basic filtering on it so they can check various scenarios "Is it only customers who are left-handed who are affected or ones with red hair as well?"

    Judging from your signature, you also deal with customer financial data and know how sensitive things can be. Once the regulators start sniffing around, you need to know exactly who is affected by an issue. They don't take prisoners.

    Again, usual stuff e.g. do a Count first and say "Is that what you really want?".

    The next issue could be "access" (RACF) rules to actually get to the data. Would the Security Police be happy with a new way of getting to the data? Financial data = lots of rules / questions.
    I'll have a look at that "pull" process and see if I can get more details on AQT as well. It may be that somebody with the purse strings says "You only need to do that in exceptional circumstances, live with what you've got".


    Steven Lamb

  • 13.  RE: DB2 z/OS query tools for large result sets

    Posted Feb 02, 2023 06:29 AM
    Are there any pretty picture Explain features built in to AQT?

    Steven Lamb

  • 14.  RE: DB2 z/OS query tools for large result sets

    Posted Feb 14, 2023 06:52 AM

    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);
      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