Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

BTEQ Utility Command

Updated on 2023-03-17 GMT+08:00

The BTEQ utility commands are as follows:

LOGOFF QUIT

LOGOFF ends the current RDBMS sessions without exiting BTEQ.

QUIT command ends the current Teradata database sessions and exits BTEQ.

Input

SELECT 'StartDTTM' as a
            ,CURRENT_TIMESTAMP (FORMAT 'HH:MI:SSBMMMBDD,BYYYY') ;
.LOGOFF;
.QUIT;

Output

SELECT 'StartDTTM' as a
            ,CURRENT_TIMESTAMP (FORMAT 'HH:MI:SSBMMMBDD,BYYYY') ;
.LOGOFF;
.QUIT;
NOTE:

Gauss does not support, so it is required to comment.

.IF ERRORCODE and .QUIT

BTEQ statements specified with .IF and .QUIT.

Input

COLLECT STATISTICS 
 USING SAMPLE 5.00 PERCENT 
 COLUMN ( CDR_TYPE_KEY ) , 
 COLUMN ( PARTITION ) , 
 COLUMN ( SRC ) , 
 COLUMN ( PARTITION,SBSCRPN_KEY ) 
 ON DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY ;
 

Output

SET
default_statistics_target = 5.00 ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (CDR_TYPE_KEY) ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (PARTITION) ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (SRC) ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (PARTITION,SBSCRPN_KEY) ;
 RESET default_statistics_target ;

.IF

Input:

.IF END_MONTH_FLAG <> 'Y' THEN .GOTO LABEL_1; 

Output:

IF END_MONTH_FLAG <> 'Y' THEN
               GOTO LABEL_1 ;
END IF ;

.QUIT

Input:

.IF ERRORCODE <> 0 THEN .QUIT 12;

Output:

IF ERRORCODE <> 0 THEN
          RAISE EXCEPTION '12' ;
END IF;

.IF & .QUIT

.IF and .QUIT should be moved inside the block.

Oracle Syntax

Syntax After Migration

INSERT INTO EMP 
SELECT * FROM EMP;
.IF ERRORCODE <> 0 THEN .QUIT 12;
DECLARE 
lv_mig_errorcode NUMBER ( 4 ) ;
BEGIN
     BEGIN
      INSERT INTO EMP 
     SELECT * FROM EMP;
     lv_mig_errorcode := 0 ;
      EXCEPTION
          WHEN OTHERS THEN
           lv_mig_errorcode := - 1 ;
     END ;
     IF lv_mig_errorcode <> 0 THEN
          RAISE EXCEPTION '12' ;
     END IF ;
 END ;
/

.RETURN

Input:

.if ERRORCODE = 0 then .RETURN; 

Output:

IF ERRORCODE = 0 THEN
          RETURN;
END IF;

.GOTO

Input:

.IF END_MONTH_FLAG <> 'Y' THEN .GOTO LABEL_1; 

Output:

IF END_MONTH_FLAG <> 'Y' THEN
      GOTO LABEL_1;
END IF ;

Label

Input:

.LABEL LABEL_1

Output:

<<LABEL_1>>

ERRORCODE 3807

Input:

SELECT end_mon AS END_MONTH_FLAG
  FROM tab2 ;

.IF END_MONTH_FLAG <> 'Y' THEN
        .GOTO LABEL_1;

.IF ERRORCODE = 3807 THEN 
       .QUIT 8888;

Output

DECLARE lv_mig_errorcode NUMBER (4);
      lv_mig_END_MONTH_FLAG TEXT;
BEGIN
     BEGIN
          SELECT end_mon 
             INTO lv_mig_END_MONTH_FLAG
            FROM tab2 ;

            lv_mig_errorcode := 0 ;
     EXCEPTION
           WHEN UNDEFINED_TABLE THEN
                 lv_mig_errorcode := 3807 ;

          WHEN OTHERS THEN
               lv_mig_errorcode := - 1 ;
     END ;

     IF lv_mig_END_MONTH_FLAG <> 'Y' THEN
               GOTO LABEL_1 ;
     END IF ;
     IF lv_mig_errorcode = 3807 THEN
          RAISE EXCEPTION '8888' ;
     END IF ;
END;
/

BT with BTEQ commands

Input

BT;

delete from ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL 
where DW_Job_Seq = ${v_Group_No};

.if ERRORCODE <> 0 then .quit 12;

insert into ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL 
(
   Cust_Id
  ,Cust_UID
  ,DW_Upd_Dt
  ,DW_Upd_Tm
  ,DW_Job_Seq
  ,DW_Etl_Dt
)
select
   a.Cust_Id
  ,a.Cust_UID
  ,current_date as Dw_Upd_Dt
  ,current_time(0) as DW_Upd_Tm
  ,cast(${v_Group_No} as byteint) as DW_Job_Seq
  ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Etl_Dt
from ${BRTL_VCOR}.BRTL_CS_CUST_CID_UID_REL_S a
where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd');

.if ERRORCODE <> 0 then .quit 12;

Output

BEGIN
--    
   BEGIN          
      delete from ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL  
       where DW_Job_Seq = ${v_Group_No};
         lv_mig_errorcode = 0;
   EXCEPTION
      WHEN OTHERS THEN
         lv_mig_errorcode = -1;
   END;

   IF lv_mig_errorcode <> 0 THEN
        RAISE EXCEPTION '12';
   END IF;

ET with BTEQ Commands

Input

ET;
BEGIN

   BEGIN          
      delete from ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL  
       where DW_Job_Seq = ${v_Group_No};
         lv_mig_errorcode = 0;
   EXCEPTION
      WHEN OTHERS THEN
         lv_mig_errorcode = -1;
   END;

   IF lv_mig_errorcode <> 0 THEN
        RAISE EXCEPTION '12';
   END IF;


   BEGIN
        insert into ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL 
        (
          Cust_Id
         ,Cust_UID
         ,DW_Upd_Dt
         ,DW_Upd_Tm
         ,DW_Job_Seq
         ,DW_Etl_Dt
       )
      select
          a.Cust_Id
         ,a.Cust_UID
         ,current_date as Dw_Upd_Dt
         ,current_time(0) as DW_Upd_Tm
         ,cast(${v_Group_No} as byteint) as DW_Job_Seq
         ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Etl_Dt
       from ${BRTL_VCOR}.BRTL_CS_CUST_CID_UID_REL_S a
     where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd');
   EXCEPTION
      WHEN OTHERS THEN
         lv_mig_errorcode = -1;
   END;

   IF lv_mig_errorcode <> 0 THEN
        RAISE EXCEPTION '12';
   END IF;

END;

Output

--     
   BEGIN
        insert into ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL 
        (
          Cust_Id
         ,Cust_UID
         ,DW_Upd_Dt
         ,DW_Upd_Tm
         ,DW_Job_Seq
         ,DW_Etl_Dt
       )
      select
          a.Cust_Id
         ,a.Cust_UID
         ,current_date as Dw_Upd_Dt
         ,current_time(0) as DW_Upd_Tm
         ,cast(${v_Group_No} as byteint) as DW_Job_Seq
         ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Etl_Dt
       from ${BRTL_VCOR}.BRTL_CS_CUST_CID_UID_REL_S a
     where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd');
   EXCEPTION
      WHEN OTHERS THEN
         lv_mig_errorcode = -1;
   END;

   IF lv_mig_errorcode <> 0 THEN
        RAISE EXCEPTION '12';
   END IF;

END;

.Export FILE

.EXPORT FILE should be changed to COPY and teradata utilities is set to false.

Oracle Syntax

Syntax After Migration

.export file = $FILENAME;
select empno, ename from emp where deptno = 1;
COPY (select empno, ename from emp where deptno = 1) TO '$FILENAME' CSV HEADER DELIMITER E'\t';

.EXPORT FILE should be changed to $q$COPY and moved inside the block when teradataUtilities is set to true.

Oracle Syntax

Syntax After Migration

print BTEQ <<ENDOFINPUT;
   DATABASE HPBUS;
.export file = $FILENAME;
select empno, ename from emp where deptno = 1;
.IF ERRORCODE <> 0 THEN .QUIT 12;
.LOGOFF;
.QUIT 0;
ENDOFINPUT
DECLARE lv_mig_obj_exists_check NUMBER(1);
 lv_mig_errorcode NUMBER(4);
BEGIN
 SET SESSION CURRENT_SCHEMA TO public ;

    BEGIN
  SELECT COUNT(*) INTO lv_mig_obj_exists_check
    FROM (select empno, ename from emp where deptno = 1);
        lv_mig_errorcode := 0;

    EXCEPTION
        WHEN OTHERS THEN
            lv_mig_errorcode := -1;
    END ;
 EXECUTE $q$COPY (select empno, ename from emp where deptno = 1) TO '$FILENAME' CSV HEADER DELIMITER E'\t'$q$;

    IF lv_mig_errorcode <> 0 THEN
          RAISE EXCEPTION '12';
    END IF ;
 RAISE EXCEPTION '-99';
    RETURN ;
END ;
/

SQL_Lang & BTEQ

Multiple tag names should be handled for SQL_Lang.

Oracle Syntax

Syntax After Migration

#!/usr/bin/perl
######################################################################
# BTEQ script in Perl
# Date Time    : 2013-10-15
# Table        : TB_NET_LTE_GPRS_CDR (4G GPRS»°µ¥)
# Script Name  : XXX00_TB_NET_LTE_GPRS_CDR
# Source Table :  TB_04024-GPRS
# Load strategy: S4
use strict; # Declare using Perl strict syntax

my $HOME = $ENV{"AUTO_HOME"};
unshift(@INC, "$HOME/bin");
require etl_pub;   

# ------------ Variable Section ------------
# DATABASE NAMES
my $TEMPDB = $ETL::TEMPDB;
my $SDATADB = $ETL::SDATADB;
my $PDATADB = $ETL::PDATADB;
my $PARADB = $ETL::PARADB;
my $PDDL = $ETL::PDDL;

my $PCODE = $ETL::PCODE;
if ( $#ARGV < 0 ) {
   exit(1);
}
my $CONTROL_FILE = $ARGV[0];
my $TX_DATE = substr(${CONTROL_FILE},length(${CONTROL_FILE})-12, 8);

open(STDERR, ">&STDOUT");
my $TRG_COL_LIST =<<TRGCOLLIST;
 MSISDN
,dat_rcd_dt
,vst_rgn_cd

TRGCOLLIST

my $MAP_COL_LIST =<<MAPCOLLIST;
 TB_04024.msisdn
 ,CAST('$TX_DATE' AS DATE FORMAT 'YYYYMMDD')
 ,TB_04024.vst_rgn_cd
MAPCOLLIST

my $FILTER = "CMCC_Prov_Prvd_Id=$PCODE";
my $table_today ="${TEMPDB}.TB_04024_${PCODE}";
my $table_target = "Z" . substr($PCODE,1,2) . "NET_LTE_GPRS_CDR";
my $UNIT_FLAG;

sub BTEQ_S4
{
 my ($dbh) = @_;
 my $BTEQ_CMD_S4 = <<ENDOFINPUT;

INSERT INTO ${PDATADB}.$table_target (
$TRG_COL_LIST)
SELECT
$MAP_COL_LIST
FROM $SDATADB.TB_${PCODE}_04024_${UNIT_FLAG}_${TX_DATE} TB_04024;
.IF ERRORCODE <> 0 THEN .QUIT 12;
ENDOFINPUT
 return $BTEQ_CMD_S4;
}
my $unit_num = "04024";

sub BTEQ_Z1
{
 my $BTEQ_CMD_Z1 = <<ENDOFINPUT;
INSERT INTO ${PDATADB}.$table_target (
$TRG_COL_LIST)
SELECT 
$MAP_COL_LIST
FROM $SDATADB.TB_${PCODE}_${unit_num}_${UNIT_FLAG}_${TX_DATE} tb_${unit_num}; 
.IF ERRORCODE <> 0 THEN .QUIT 12;
ENDOFINPUT
 return $BTEQ_CMD_Z1;
}

sub main()
{
 my $dbh=ETL::DBconnect();
# SDATA 
 $UNIT_FLAG = ETL::get_UNIT_FLAG($dbh, "TB_04024", $PCODE, $TX_DATE);
 my $BTEQCMD = "";
 if ( $UNIT_FLAG eq " " ) {
  print "Ô!\n";
  ETL::disconnectETL($dbh);
  return 1;
 } elsif ($UNIT_FLAG eq "S") {
  $BTEQCMD = BTEQ_S4($dbh);
 } elsif ($UNIT_FLAG eq "Z") {
  $BTEQCMD = BTEQ_Z1($dbh);
 } else {
  print "\n";
  return 1;
 }
 ETL::disconnectETL($dbh);

 return ETL::ExecuteBTEQ($BTEQCMD, $TX_DATE);
}
my $ret= main();
exit($ret); 
#!/usr/bin/perl
######################################################################
# BTEQ script in Perl
# Date Time    : 2013-10-15
# Table        : TB_NET_LTE_GPRS_CDR (4G GPRS)
# Script Name  : XXX00_TB_NET_LTE_GPRS_CDR
# Source Table :  TB_04024-GPRS
# Load strategy: S4
use strict; # Declare using Perl strict syntax

my $HOME = $ENV{"AUTO_HOME"};
unshift(@INC, "$HOME/bin");
require etl_pub;    

# ------------ Variable Section ------------
# DATABASE NAMES
my $TEMPDB = $ETL::TEMPDB;
my $SDATADB = $ETL::SDATADB;
my $PDATADB = $ETL::PDATADB;
my $PARADB = $ETL::PARADB;
my $PDDL = $ETL::PDDL;

my $PCODE = $ETL::PCODE;
if ( $#ARGV < 0 ) {
   exit(1);
}
my $CONTROL_FILE = $ARGV[0];
my $TX_DATE = substr(${CONTROL_FILE},length(${CONTROL_FILE})-12, 8);

open(STDERR, ">&STDOUT");
my $TRG_COL_LIST=<<TRGCOLLIST ;
MSISDN
,dat_rcd_dt
,vst_rgn_cd

TRGCOLLIST

my $MAP_COL_LIST=<<MAPCOLLIST ;
TB_04024.msisdn
,CAST( '$TX_DATE' AS DATE )
,TB_04024.vst_rgn_cd
MAPCOLLIST

my $FILTER = "CMCC_Prov_Prvd_Id=$PCODE";
my $table_today ="${TEMPDB}.TB_04024_${PCODE}";
my $table_target = "Z" . substr($PCODE,1,2) . "NET_LTE_GPRS_CDR";
my $UNIT_FLAG;


sub BTEQ_S4
{
 my ($dbh) = @_;
my $BTEQ_CMD_S4=<<ENDOFINPUT ;

DECLARE lv_mig_obj_exists_check NUMBER ( 1 ) ;
lv_mig_errorcode NUMBER ( 4 ) ;

BEGIN


     
     BEGIN
          INSERT INTO ${PDATADB}.$table_target ($TRG_COL_LIST)
          SELECT
                    $MAP_COL_LIST
               FROM
                    $SDATADB.TB_${PCODE}_04024_${UNIT_FLAG}_${TX_DATE} TB_04024 ;
                    lv_mig_errorcode := 0 ;

               EXCEPTION
                    WHEN OTHERS THEN
                    lv_mig_errorcode := - 1 ;

     END ;
     IF lv_mig_errorcode <> 0 THEN
          RAISE EXCEPTION '12' ;

     END IF ;

END ;
/
ENDOFINPUT
 return $BTEQ_CMD_S4;
}
my $unit_num = "04024";

sub BTEQ_Z1
{
my $BTEQ_CMD_Z1=<<ENDOFINPUT ;

DECLARE lv_mig_obj_exists_check NUMBER ( 1 ) ;
lv_mig_errorcode NUMBER ( 4 ) ;

     BEGIN
          INSERT INTO ${PDATADB}.$table_target ($TRG_COL_LIST)
          SELECT
                    $MAP_COL_LIST
               FROM
                    $SDATADB.TB_${PCODE}_${unit_num}_${UNIT_FLAG}_${TX_DATE} tb_${unit_num} ;
                    lv_mig_errorcode := 0 ;

               EXCEPTION
                    WHEN OTHERS THEN
                    lv_mig_errorcode := - 1 ;

     END ;
     IF lv_mig_errorcode <> 0 THEN
          RAISE EXCEPTION '12' ;

     END IF ;

END ;
/
ENDOFINPUT
 return $BTEQ_CMD_Z1;
}

sub main()
{
 my $dbh=ETL::DBconnect();
# SDATA 
 $UNIT_FLAG = ETL::get_UNIT_FLAG($dbh, "TB_04024", $PCODE, $TX_DATE);
 my $BTEQCMD = "";
 if ( $UNIT_FLAG eq " " ) {
  print "!\n";
  ETL::disconnectETL($dbh);
  return 1;
 } elsif ($UNIT_FLAG eq "S") {
  $BTEQCMD = BTEQ_S4($dbh);
 } elsif ($UNIT_FLAG eq "Z") {
  $BTEQCMD = BTEQ_Z1($dbh);
 } else {
  print "¸\n";
  return 1;
 }
 ETL::disconnectETL($dbh);

 return ETL::ExecuteBTEQ($BTEQCMD, $TX_DATE);
}
my $ret= main();
exit($ret); 

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback