datastage调用存储过程

Myth丶恋晨 2022-02-23 09:20 376阅读 0赞

创建测试表

create table DB2INST1.PROC_RUN_LOG(
        ETL_DT varchar(8)  not null,
        PROC_NAME varchar(50) not null,
        RUN_TIME timestamp
    );

创建测试存储过程

\#PROC\_TEST1

CREATE PROCEDURE "DB2INST1"."PROC_TEST1"  (ETL_DT varchar(8))
    	LANGUAGE SQL
    BEGIN
    	insert into DB2INST1.PROC_RUN_LOG values(ETL_DT,'DB2INST1.PROC_TEST1',current timestamp);
    END

\#PROC\_TEST2

CREATE PROCEDURE "DB2INST1"."PROC_TEST2"  (ETL_DT varchar(8),OUT LOGCOUNT int)
    	LANGUAGE SQL
    BEGIN
        declare sqlcode INTEGER DEFAULT 0;
        declare sqlstate CHAR(5);
        declare v_step int;
        declare err_text varchar(1024);
        declare message_text varchar(1024);
    
        -- 定义异常处理
        declare exit handler for SQLEXCEPTION
        begin
        get diagnostics exception 1  err_text = message_text;
        set message_text=trim('v_step='||char(v_step))||' '||substr(err_text,1,8)||substr(err_text,locate('"',err_text)-1);
        SIGNAL SQLSTATE '72342' SET message_text=message_text;
        end;
        set v_step=1;
    	insert into DB2INST1.PROC_RUN_LOG values('20190407','DB2INST1.PROC_TEST1',current timestamp);
        select count(1) into LOGCOUNT from DB2INST1.PROC_RUN_LOG;
    END

PROC\_TEST1对应的DS（存储过程只带输入参数）

(1)配置DS参数

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70][]

(2)引用相关stage

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 1][]

(3)配置Stored\_Procedure\_0

Genaral标签下，选择数据库类型，配置数据库名，用户名，密码

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 2][]

Syntax标签下，配置存储过程名及调用语法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 3][]

（3）编译运行作业即可

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 4][]

PROC\_TEST2对应的DS（存储过程带输入输出参数）

(1)配置DS参数

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70][]

(2)引用相关stage

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 1][]

(3)配置Stored\_Procedure\_0

Genaral标签下，选择数据库类型，配置数据库名，用户名，密码

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 2][]

Syntax标签下，配置存储过程名及调用语法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 5][]

Out-->Columns列下添加输出列（如有多个输出列时，在此配置全部输出列的名称）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 6][]

Stage-->Parameters下配置映射关系（如果有多个输出列时，在此配置多行映射）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 7][]

保存编译运行作业即可

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 8][]

如有疑问，可加微信一起交流

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 9][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70]: /images/20220223/b67ecf2fb1fa488c857a58c107777703.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 1]: /images/20220223/3e7012b31cad473dafd1bdecbaeb571d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 2]: /images/20220223/e31a332aedc04c11b15ce5b37be7e50b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 3]: /images/20220223/cd481d374002478e98c3061ee3af59b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 4]: /images/20220223/c6a0203c257f44ce98e2aeac8aee0918.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 5]: /images/20220223/4bd937862f3b4fd3af52e250645d32c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 6]: /images/20220223/44c1aa931db644158ca9c5da8d4d17c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 7]: /images/20220223/93604c65af2a49bf8edc8bfa75c2841e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 8]: /images/20220223/4a37697487784023a12c0fb4420c949b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 9]: /images/20220223/9749b4b3940b40e48b08973fcf1f6223.png