Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    9

    Unanswered: unload data using a view

    hi

    I want to unload the data from a view.(z/os DB2). Is there any way to select data to unload from a view.
    right now I am using this to unload the whole table

    UNLOAD TABLESPACE R1BANCS.TSMFFM DELIMITED COLDEL '-'
    FROM TABLE
    "R1BANCS"."MFFM"

    how can I unload only from the view (eg:MFFM_VIEW)

    Thanks
    Navid

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    how can I unload only from the view (eg:MFFM_VIEW)
    I don't know from view.
    But, you can specify DATA option to unload columns of tables in a tablespace.

    Here is a description in the manual "DB2 Version 9.1 for z/OS Utility Guide and Reference"
    Option descriptions

    DATA
    Identifies the data that is to be selected for unloading with table-name in the
    from-table-spec. The DATA keyword is mutually exclusive with TABLESPACE,
    PART, and LIST keywords.


    When you specify the DATA keyword, or you omit either the TABLESPACE or
    the LIST keyword, you must also specify at least one FROM TABLE clause.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Navid2000, Going by your syntax, I believe you are using the Unload utility. This Utility is very restrictive on what it can do. It unloads by accessing a Table space. If you only have one table per table space, you can only unload the one table. If you have 2 or more tables in the same table space, you can unload from those tables with one Unload Utility statement.

    Also, you can not join tables (or use subqueries, I believe). If your view does any of these things, it can't be used. And because of its restrictive nature, I would be surprised if it can unload from a View at all (there is no mention of this ability).

    You should have the IBM DSNTIAUL program available. If you use this with the PARMS(SQL) option, it indicates it will accept any valid dynamic SQL Statement. Since using a View is valid in dynamic SQL, you should be able to use DSNTIAUL program to Unload using a View. But I don't think you can unload to a CSV file as you are trying to do.

    There is a third option but you have to pay for it. There is an IBM High Performance Unload (HPU). When you supply HPU with an SQL statement that it can't not process directly, it call DSNTIAUL to perform the SQL. You can define CSV output and I believe it will the output will be in CSV format even if the SQL was passed to DSNTAIUL.

  4. #4
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    You can unload from view using DSNTIAUL in z/OS as stealth_DBA mentioned. The job card of this type some thing like as below.

    //SYSTSIN DD *
    DSN SYSTEM(DBSYSTEM)
    RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')
    //SYSIN DD *
    SELECT * FROM SCHEMA.VIEWNAME
    ;

  5. #5
    Join Date
    Nov 2009
    Posts
    9
    Thanks guys. Appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •