 |
 |
Archives of the TeradataForum
Message Posted: Tue, 22 Oct 2002 @ 16:12:11 GMT
| 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
| |
