![]() Use the WLM environment option to specify which Workload Manager environment will be used to debug the procedure. Use the Enable debugging option to specify whether this procedure is available to be debugged. If you’re new to SQL and want more information about embedding SQL in host languages, there’s a lot of information about this topic in the Db2 for z/OS documentation. Note: Not all options mentioned above are displayed due to screen size limit. ![]() By default, Db2 activates the first version of a stored procedure. If other versions of the procedure do not already exist, the procedure is created and activated upon deployment. Treat duplicate deployments as errors returns an error if the procedure already exists. Optionally, you can choose not to activate the procedure upon deployment otherwise, it’s activated by default.Įxception: If you’re using CREATE OR REPLACE PROCEDURE, which is available in Db2 12 function level 507 or later, this option does not result in any modifications.ĭrop duplicates calls DROP PROCEDURE before running the CREATE PROCEDURE DDL if the procedure already exists, creates the procedure, and activates it upon deployment. Use the Duplicate handling option to specify the behavior of the deployment if the procedure already exists:Īlter duplicates modifies the CREATE PROCEDURE DDL to ALTER PROCEDURE if the procedure already exists. You can specify multiple schemas, each one separated by a comma. Use the Default path option for resolving an unqualified data type, function, or procedure referenced by the procedure that’s being deployed. ![]() If currentSQLID is not set, the JDBC connection username will be used. If this field is left empty, the JDBC property currentSQLID will be used. Use the Build owner option to specify the owner of the NSP. If currentSchema is not set, the JDBC connection username will be used. If you leave this field empty, the JDBC property currentSchema will be used. If the NSP is qualified (like the one in our example CREATE PROCEDURE statement, which is qualified to ADMF001), the field is disabled. This field is enabled only for unqualified NSPs. Use the Target schema option for unqualified NSPs. See Creating a database connection for more information. Use the Database connection option to select a connection from the list of defined connections. The Deployment view consists of two sections: Deployment options and Routine options. These options are used only during deployment and do not impact the DDL source code. This is where you can set and customize deployment options and save the options for the. Deploying a stored procedureĬlick the Deploy action to open the Deployment view. We’ll cover each one in the following sections. spsql file in Db2 Developer Extension, you get some additional actions in the toolbar in the upper right corner of the view:įrom left to right, these actions are Deploy, Debug, and Run. spsql identifies it as a stored procedure. Here’s how that same stored procedure looks in Db2 Developer Extension: SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_SALARY + EMPLOYEE_BONUS You can paste it into a file so that you try things out for yourself.ĬREATE PROCEDURE ADMF001.RETURNDEPTSALARYĭECLARE TOTAL_SALARY DECIMAL(15,2) DEFAULT 0 įETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS This NSP will read the total salary, including bonuses, from the table DSN8D10.EMP from a passed in-parameter DEPTNUMBER. We’ll use the following example stored procedure throughout this article to demonstrate how to deploy, run, and debug an NSP. When NSP options are specified, only the first NSP in the file will be executed additional NSPs and SQL statements will be ignored. Note: Currently, Db2 Developer Extension deploy, debug, and run options support only one NSP per. Db2 Developer Extension uses the number sign character (#) as the default terminator character, but you can use any terminator character you want by setting a SQL routine option (explained later in this article). One big advantage of using Db2 Developer Extension to deploy an NSP, as opposed to manually executing SQL, is that you can save the various deployment options separately from the SQL itself, which means that you can push your code into a source code manager, such as GitHub, without having to remove the deployment debug options first.Īnother advantage is that you don’t need to specify -#SET TERMINATOR in the header of. Tip: If you’re new to stored procedures, make sure you’re familiar with the information in Creating stored procedures first. It also walks you through the process of debugging and running an NSP. This article shows you how to use Db2 Developer Extension to deploy a native SQL stored procedure (NSP) with various deployment options: enabling debug, altering previous deployments, setting target schema, and others. ![]() Deploying, running, and debugging stored procedures ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |