Db2 for z/OS & Db2ZAI

 View Only
  • 1.  Unload FROMCOPY using a select statement

    Posted Fri April 09, 2021 02:04 PM

    I'm trying to figure out how to do an unload from a full image copy, while using a select statement. I know we can do this with a normal unload from the active table, and I have tried using the same syntax but my unload fails. The IBM documentation states that you can do a a select just like any other unload. I have not been able to as of yet. Has anyone else been successful with this. I can't seem to be able to find any examples of this syntax online anywhere either. I resorted to just doing a full unload of the image copy and loading that to the table. Then doing a selective unload of the data that I needed, and replacing the data in the table with that unload. It would save a lot of time and resources if I could get the data that we need in the first unload. 

    The IBM documentation states

    You can select specific rows and columns to unload just as you would for a table space. You can specify the selection criteria with either the PART keyword, the FROM TABLE clause, or both, to qualify tables and rows that are to be unloaded. However, do not include LOB columns in the field specification list. You can unload rows that contain LOB columns only when the LOB columns are excluded.

    Syntax I  have tried:
    UNLOAD TABLESPACE <DB>.<TS>
    SELECT CIT_POLICY_ID_NBR FROM <SCHEMA>.<TABLE>
    FROMCOPY <IMAGE COPY DSN>
    WHERE <SELECTION CRITERIA> 

    The error that I get:
    DSNU049I 098 15:58:54.51 DSNUGPRS - INVALID OPERAND 'SELECT' FOR KEYWORD 'UNLOAD'



    ------------------------------
    Robert Goodell
    ------------------------------

    #Db2forz/OS


  • 2.  RE: Unload FROMCOPY using a select statement

    IBM Champion
    Posted Mon April 12, 2021 01:45 AM

    Hi!

    Pretty sure you cannot use SELECT in the syntax, you get every column that is there but you can use WHERE to restrict the number of rows you get.

     

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-


    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email:
    R.Boxwell@seg.de
    Web  http://www.seg.de

    Link zur Datenschutzerklärung


    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

     






  • 3.  RE: Unload FROMCOPY using a select statement
    Best Answer

    Posted Mon April 12, 2021 01:57 AM
    Try:

    UNLOAD TABLESPACE <DB>.<TS>
    FROMCOPY <IMAGE COPY DSN>
    FROM TABLE <SCHEMA>.<TABLE>
    WHEN ( <SELECTION CRITERIA> )

    ------------------------------
    JAMES CAMPBELL
    ------------------------------



  • 4.  RE: Unload FROMCOPY using a select statement

    Posted Mon April 12, 2021 04:32 AM
    Hello Robert,

    You can unload individual columns, but not in the form of a SELECT list. The syntax looks like this:

    UNLOAD TABLESPACE DSN8D12A.DSN8S81E NOPAD
    FROMCOPY <inage_copy_dsn>
    FROM TABLE DSN8C10.EMP
    (EMPNO, LASTNAME, SALARY DECIMAL EXTERNAL)
    WHEN (WORKDEPT = 'D11' AND SALARY > 25000)

    The field specification allows you to specify output options (as in SALARY DECIMAL EXTERNAL).

    Kind regards,

    ------------------------------
    Gareth Copplestone-Jones
    ------------------------------



  • 5.  RE: Unload FROMCOPY using a select statement

    Posted Mon April 12, 2021 08:29 AM

    Thank you Gareth! This is exactly what I was looking for. I knew it had to be something with my syntax that was off. This worked perfectly for what I needed.

     

    Thanks,

    Robert W Goodell

    DB2/IMS DBA

    Email: rgoo6@allstate.com

    Phone: 330-656-6692

    Help save a tree.
    Please print this email only if necessary.

    isg_dba

     


    Internal Information






  • 6.  RE: Unload FROMCOPY using a select statement

    Posted Tue April 13, 2021 09:11 AM
    UNLOAD TABLESPACE ORCDB001.ORCTS051 NOPAD  
    FROMCOPY ORCP.IMG.ORCDB001.ORCTS051.G1645V00
      PUNCHDDN SYSPUNCH                        
      UNLDDN   SYSREC                          
     FROM TABLE ORCDB2.ORC_MDSE_STR            
     WHEN (MST_COMPANY = '01'                  
      AND MST_VND_ORDER_NUM = '0071254704')     

    --
    "Last but not least, avoid cliches like the plague."


    [Dillards Confidential]
      Michael D. High
      Senior DBA
      Dillard's Information Technology
     (501) 399-7378





  • 7.  RE: Unload FROMCOPY using a select statement

    Posted Tue April 13, 2021 09:12 AM
    It will work when you use WHEN instead of SELECT

    when (column-a='xyz' and column-b = 999 and..... etc)

    ------------------------------
    Lars Sørensen
    ------------------------------