Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 22 Oct 2002 @ 16:12:11 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Producing large extract files
 
From:   David Ahnell

We have a need to produce a very large, fixed length, non-delimited character data extract for input into a 3rd party product. In using BTEQ to try and accomplish this we have run into 2 problems. The first issue is that the return of the result set to disk is taking very long, 375 minutes per GB or around 200 rows per second. The second issue is that the process is ending with the following messages when the file reaches a size of 1,073,741,312 bytes.

*** Error: File too large
*** Warning: Canceling the rest of the output.

The file system is not anywhere near out of space, and I don't see any such file size limitation in the BTEQ manuals. We're using BTEQ version 6.01.00.00. We've tried running this on both and IBM AIX machine and one of our NCR MP/RAS nodes with the same results.

I've attached the BTEQ script, but am also wondering how others accomplish such a task, with examples if possible. Note: the LIMIT value set in the script is far above the actual and expected number of rows produced. It's been tried with and without a LIMIT value specified.

.SET SESSIONS 1
.LOGON azb,az00

.SET DEFAULTS
.SET FORMAT OFF
.SET ECHOREQ OFF
.SET SEPARATOR 0
.SET RETCANCEL ON
.SET WIDTH 254
.SET PAGEBREAK OFF
.SET TITLEDASHES OFF
.SET ERROROUT STDERR

.OS rm /export/symmetry_file.msg
.MESSAGEOUT FILE=/export/symmetry_file.msg

.OS rm /export/symmetry_file.dat
.EXPORT REPORT FILE=/export/symmetry_file.dat ,LIMIT=100000000

SELECT  CASE ch.sbscrbr_id
        WHEN 'N/A' THEN '                 '
        ELSE ch.sbscrbr_id
        END (TITLE ''),
        CASE ch.mbr_id
        WHEN 'N/A' THEN '  '
        ELSE SUBSTR(ch.mbr_id,2,2)
        END (TITLE ''),
        CASE ch.mbr_gndr_cd
        WHEN 'N/A' THEN ' '
        ELSE SUBSTR(ch.mbr_gndr_cd,1,1)
        END (TITLE ''),
        SUBSTR(CAST((ch.mbr_age_num (FORMAT 'ZZZZ'))
        AS CHAR(4)),1,3) (TITLE ''),
        CASE WHEN cl.pd_amt < 0
        THEN CAST((cl.pd_amt (FORMAT '-9999999.99'))
                AS CHAR(11))
        ELSE CAST((cl.pd_amt (FORMAT '99999999.99'))
                AS CHAR(11))
        END (TITLE ''),
        CASE WHEN cl.alowd_amt < 0
        THEN CAST((cl.alowd_amt (FORMAT '-9999999.99'))
                AS CHAR(11))
        ELSE CAST((cl.alowd_amt (FORMAT '99999999.99'))
                AS CHAR(11))
        END (TITLE ''),
        CASE cl.prcdr_cpt_cd
        WHEN 'N/A' THEN '     '
        ELSE CAST(cl.prcdr_cpt_cd AS CHAR(5))
        END (TITLE ''),
        CASE cl.prcdr_cpt_mdfr_1_cd
        WHEN 'N/A' THEN '  '
        ELSE SUBSTR(cl.prcdr_cpt_mdfr_1_cd,1,2)
        END (TITLE ''),
        CASE WHEN ch.dgns_icd_1_cd = 'N/A'
        THEN '     '
        WHEN ch.clm_type_cd = 'HO'
        THEN SUBSTR(ch.dgns_icd_1_cd,1,5)
        ELSE SUBSTR(cl.dgns_icd_prmry_cd,1,5)
        END (TITLE ''),
        CASE WHEN ch.dgns_icd_2_cd = 'N/A'
        THEN '     '
        WHEN ch.clm_type_cd = 'HO'
        THEN SUBSTR(ch.dgns_icd_2_cd,1,5)
        ELSE '     '
        END (TITLE ''),
        CASE WHEN ch.dgns_icd_3_cd = 'N/A'
        THEN '     '
        WHEN ch.clm_type_cd = 'HO'
        THEN SUBSTR(ch.dgns_icd_3_cd,1,5)
        ELSE '     '
        END (TITLE ''),
        CASE WHEN ch.dgns_icd_4_cd = 'N/A'
        THEN '     '
        WHEN ch.clm_type_cd = 'HO'
        THEN SUBSTR(ch.dgns_icd_4_cd,1,5)
        ELSE '     '
        END (TITLE ''),
        CAST(CAST(cl.first_srvc_dt
                AS FORMAT 'YYYYMMDD')
                AS CHAR(8)) (TITLE ''),
        CAST(CAST(cl.last_srvc_dt
                AS FORMAT 'YYYYMMDD')
                AS CHAR(8)) (TITLE ''),
        CAST(CAST(cl.paid_dt
                AS FORMAT 'YYYYMMDD')
                AS CHAR(8)) (TITLE ''),
        CASE cl.type_srvc_hcfa_cd
        WHEN 'N/A' THEN '   '
        ELSE CAST(cl.type_srvc_hcfa_cd AS CHAR(3))
        END (TITLE ''),
        CASE cl.srvcng_prvdr_algnd_id
        WHEN 'N/A' THEN '               '
        ELSE CAST(cl.srvcng_prvdr_algnd_id AS CHAR(15))
        END (TITLE ''),
        CASE cl.srvcng_prvdr_type_cd
        WHEN 'N/A' THEN '    '
        ELSE CAST(cl.srvcng_prvdr_type_cd AS CHAR(4))
        END (TITLE ''),
        CASE cl.ndc_cd
        WHEN 'N/A' THEN '           '
        ELSE CAST(cl.ndc_cd AS CHAR(11))
        END (TITLE ''),
        '        ' (TITLE ''),
        '        ' (TITLE ''),
        CASE cl.atndg_prvdr_algnd_id
        WHEN 'N/A' THEN '               '
        ELSE CAST(cl.atndg_prvdr_algnd_id AS CHAR(15))
        END (TITLE ''),
        CASE ch.ub92_type_bill_3_algnd_cd
        WHEN 'N/A' THEN ' '
        ELSE SUBSTR(ch.ub92_type_bill_3_algnd_cd,1,1)
        END (TITLE ''),
        CASE ch.prcdr_icd_1_cd
        WHEN 'N/A' THEN '    '
        ELSE SUBSTR(ch.prcdr_icd_1_cd,1,4)
        END (TITLE ''),
        CASE ch.prcdr_icd_2_cd
        WHEN 'N/A' THEN '    '
        ELSE SUBSTR(ch.prcdr_icd_2_cd,1,4)
        END (TITLE ''),
        CASE ch.prcdr_icd_3_cd
        WHEN 'N/A' THEN '    '
        ELSE SUBSTR(ch.prcdr_icd_3_cd,1,4)
        END (TITLE ''),
        CASE ch.prcdr_icd_4_cd
        WHEN 'N/A' THEN '    '
        ELSE SUBSTR(ch.prcdr_icd_4_cd,1,4)
        END (TITLE ''),
        cl.extrct_src_cd (TITLE ''),
        ' ' (TITLE ''),
        CASE cl.clm_pk
        WHEN 'N/A' THEN '               '
        ELSE CAST(cl.clm_pk AS CHAR(15))
        END (TITLE ''),
        CASE cl.sqnc_num
        WHEN 'N/A' THEN '    '
        ELSE CAST(cl.sqnc_num AS CHAR(4))
        END (TITLE ''),
        CASE cl.line_num
        WHEN 'N/A' THEN '   '
        ELSE CAST(cl.line_num AS CHAR(3))
        END (TITLE ''),
        CASE WHEN cl.chrg_amt < 0
        THEN CAST((cl.chrg_amt (FORMAT '-9999999.99'))
                AS CHAR(11))
        ELSE CAST((cl.chrg_amt (FORMAT '99999999.99'))
                AS CHAR(11))
        END (TITLE '')
FROM    claim_header ch
JOIN    claim_line cl
ON      (ch.clm_pk = cl.clm_pk AND
         ch.sqnc_num = cl.sqnc_num AND
         ch.clm_num = cl.clm_num)
WHERE   ch.clm_type_cd <> 'DE'
AND     cl.clm_audt_line_flag_ind <> 'I'
ORDER BY ch.sbscrbr_id, ch.mbr_id,
        cl.first_srvc_dt, cl.last_srvc_dt;

.LOGOFF
.EXIT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016