How to execute db2 stored procedure in sql developer

 

How to execute db2 stored procedure in sql developer. The workaround for us is to drop and recreate the procedure, after which it picks up the right plan. Query: Feb 16, 2022 · from employees. TABLE_NAME Is there a better way to do this? Thanks in advance. We Feb 20, 2015 · 12. A typical example: if you got a stored procedure by the name update_employee, the below query works: You can also call stored procedures that are created from supported host languages with the Command Line Processor. testProc. Description of this image; Change the default values to 149 for EMP_ID and 2000 for SALES_AMT and click OK. Just visit our website, select a language, type in your code and hit "Run!" Write your code without having to spend hours figuring out how to set up a programming environment. When you issue COMMIT or ROLLBACK statements in your stored procedure, Db2 commits or rolls back all changes within the unit . routines will give you the Stored Procedure content. It's pretty gnarly to write this by hand, if you're doing many of these best to write some sort of generator based on your table definition. , vSchema). Jan 9, 2011 · I am looking for some help in getting the following stored proc to work: I get anonymous block completed and not able to see the result. This specifies that the procedure should return results to the caller. When creating the data source connection in Tableau - created an initial SQL to call the stored procedure: You can then add a New Custom SQL that selects the data from the SESSION table. One of the advantages of using stored procedures is that for distributed applications, the processing of one CALL statement on the Jul 25, 2012 · Found the answer, using db2expln executable from the command window, I was able to generate the execution plan for the stored procedures. This job is run during the installation and migration process. Then the procedure should return without closing the cursor. Same SQL when run from command line runs very fast. May 20, 2021 · You cannot send operating-system signals to Db2-stored-procedures (for example, you cannot send a Control-C to a Db2-stored-procedure). If the stored procedure completes without error, and returns a non-empty result-set then you will see the result-set in the lower pane "Script Output". right-click the connection and choose SQL worksheet. The DB2 STORED PROCEDURE are the programs which are directly managed by DBMS. 53. Jan 27, 2023 · Copy the SQL statement, paste it into the “SQL Commander” tab, and update the query accordingly. Yo can use a DB2® stored procedure wherever procedures are allowed, such as the destination of a Direct Transfer, within an LSX script, or in an Advanced RealTime activity such as Virtual Agents or Virtual Documents. For example, a file named myscript. ly/3tqOiprIn today's Advanced SQL lesson we walk through how to use Stored Procedures. But when i execute: execute temp_proc; it shows ORA-00900: invalid SQL statement. jar). Stored Procedures. According to IBM, it's caused by: NO AUTHORIZED routine-type BY THE NAME routine-name HAVING COMPATIBLE ARGUMENTS WAS FOUND. These can be called within a simple SELECT statement, so you'd be able to use those within Tableau Prep. In the c subdirectory under the sample directory contains files that contain sample stored procedures. select. Connect to the GSDB sample database and create a data development project to work with the database. , SOURCE_SCHEMA, TARGET_SCHEMA) and am retrieving the schema name I want to work with as a variable in the DB2 stored procedure (e. Stored procedures can be used in both distributed and nondistributed DB2 applications. Now it does what I need it to do. The STORED PROCEDURES can be used for the SQLs Db2 is not the commit coordinator. g, instead of median_result_set (?), I should do SCHEMANAME. ROUTINES WHERE SPECIFIC_SCHEMA = 'MYSCHEMA'; If you decide to limit the results by having a where clause, please note that it Apr 28, 2023 · An example of a stored procedure in SQL Server is: CREATE PROCEDURE getCurrentDate AS SELECT GETDATE; This procedure, like the example for Oracle, will return the current date. Share Improve this answer Jan 14, 2021 · Add a comment. Db2 implicitly issues the command STOP PROCEDURE ACTION Nov 5, 2018 · Too many errors Try this:--#SET TERMINATOR @ CREATE OR REPLACE PROCEDURE get_users( in offsetValue integer, in sortCol varchar(20) ) SPECIFIC get_users DYNAMIC RESULT SETS 1 LANGUAGE SQL Re: BEGIN DECLARE SELECT_STATEMENT VARCHAR(8000); DECLARE SQL_STATEMENT STATEMENT; DECLARE c_emp CURSOR WITH RETURN FOR SQL_STATEMENT; set SELECT_STATEMENT = 'select * from users'; PREPARE SQL_STATEMENT Apr 20, 2012 · To enable DB2 in SQL Developer, you need to pull out the db2jcc. Nov 19, 2016 · DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=CANCEL_ACTIVITY;PROCEDURE, DRIVER=4. Oct 17, 2021 · A WHILE loop example. END. The function must exist at the current server Mar 10, 2005 · This mode lets you run Db2 commands or SQL statements from a command script, a text file that contains the commands and statements in the order in which they are to be executed. For this use the below command to create a database named GeeksforGeeks. Parameters can be supplied to the above SP if they are Oct 11, 2019 · 3. It has a nice ui for db developers. EXEC [TEST01V]. You cannot "select" from a result set returned by a stored procedure. WHERE DASTAT='RT' AND DADTRS BETWEEN SUBSTR(dtStart, 1, 6) AND SUBSTR(dtEnd, 1, 6); IBM Data Studio looks like this: As you can see above, when it fails, it tells me INTO clause missing from embedded statement. Click on "New Connection". I have a linked server from SQL Server 2008 to DB2. you can use the db2look clp command with the -e option, altough it does not have an option to only export procedures, so unless they are in a schema without other objects you will also get e. jar that IBM Data Studio is using (along with the same db2jcc_license_cisuz. PUT_LINE('Test'); end it is created successfully. EXEC ('use testdb; EXEC TestProcedure') at [TEST01V] Following is the example of executing a stored procedure on the linked server using four-part notation. the declaration statement is pasted into my scr May 8, 2024 · sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. DECLARE SELECT_STATEMENT VARCHAR(8000); DECLARE cursor1 CURSOR WITH RETURN FOR SQL_STATEMENT; build dynamic sql here PREPARE SQL_STATEMENT FROM SELECT_STATEMENT; OPEN cursor1; To be clear, SQL_STATEMENT is any name you want. Here “ TEST01V” is the server name, “ test ” is the database name, and “ dbo ” is the schema name. Notice that the values for EMP_ID and SALES_AMT are currently set to 1. Nov 8, 2019 · If your Db2-server runs on Linux/Unix/Windows then you can use the DBMS_OUT. jar under d:\software directory. DbVisualizer will show a “Success” status in the Log section. DB2® stored procedure support provides a way for an SQL application to define and then call a procedure through SQL statements. If a COMMIT or ROLLBACK statement in a stored procedure violates any of these conditions, Db2 puts the transaction in a must-rollback state. I do not want to pass in any parameter, but I just want the Records to be returned back from the procedure into a result set -> a suitable variable. DLRINVAPF. Click the DB2 tab. No rows were affected SQLWarning: Code: 20480 SQL State: 0168Y --- The newly defined object "TEST Aug 27, 2013 · 5. How to generate the script? Ask Oracle SQL Developer (Version 4. jar? If it works correctly fro IBM Data Studio (which normally uses db2jcc4. So this works from SQL Server: exec ('call RERTEBT. The Db2 command STOP PROCEDURE prevents Db2 from accepting SQL CALL statements for one or more stored procedures. bat: echo off sqlplus username/password@databasename @myscript. The STORED PROCEDURE processes the query and returns the result to the application program. This same approach works in Alteryx. 1. Grants the privilege to run the identified user-defined function, cast function that was generated for a distinct type, or stored procedure. My ORACLE SQL Developer Version is 4. DECLARE v_now TIMESTAMP DEFAULT CURRENT TIMESTAMP; WHILE (in_iterations > 0) DO. TABLE1); OK. SELECT DISTINCT DAMDLN, Count(*) FROM TEST_DATA. How do I execute the procedure? Below are the commands I had run to execute. Set the input parameter, then click 'Ok' - the procedure will run, and you can see your refcursor below. Creating stored procedures. To be more specific, depending on the platform (this works with Oracle and SQL Sever and perhaps other platforms), you could convert your Stored Procedure to a Table-Valued Function. Thus there is no need to use report pre-SQL or a separate SELECT statement. CREATE PROCEDURE us_customers AS SELECT customer_id, first_name. A valid connection has been lost between your client and the Db2 server. The stored procedure being called must have its parameters set up correctly for the call. BEGIN procedurename (); END; If there are any parameters then it has to be passed. Click the Save button to save the connection and then click Jul 5, 2013 · 5. answered Jul 9, 2013 at 14:01. The spserver shared library contains a number of stored procedures that can be created from the spserver. Method #2, better: dump STRING_SPLIT into a temp table first. INSERT_A (inputVar1 Integer, inputVar2 Integer) LANGUAGE SQL BEGIN INSERT INTO DB2ADMIN. Description for GRANT (function or procedure privileges) EXECUTE. paste your query into the worksheet. io 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. Instead of defining a stored procedure, you can call the existing DB2 service like this: call qsys2. create proc stored_proc_name @string_input varchar(20) as declare @ temp_string varchar(20) select @temp_string from table where conduction return 0 fail Jun 18, 2012 · 3. Jun 13, 2018 · I'd separate the SQL/SQL*Plus commands to an separate script: myscript. Click the Add Entry… button. db2 "select * from syscat. The inputted xml is pretty simple, and attribute based. Link to SQL Code: https:/ Jul 1, 2015 · I am storing schema names in table columns (e. in the DBMS Output window, click the green plus icon, and select your connection. It looks like a script could do the job. Try this out next time you need to build some dynamic SQL statements. sql ! May 28, 2014 · May 28, 2014 at 10:15. In another forum I read that I have to query the table syscat. Here’s how that same stored procedure looks in Db2 Developer Extension: The file extension of . 5 handles stored-procedure output parameters in a more hostile manner compared to other GUI database tools. Jan 30, 2024 · In Object Explorer, connect to an instance of SQL Server or Azure SQL Database, expand that instance, and then expand Databases. The procedure should look like this: IN shipNo VARCHAR(30) ) DECLARE likeShipNo VARCHAR(32); SET likeShipNo = '%' || shipNo To debug stored procedures by using the Unified Debugger: Set up the Unified Debugger by performing the following steps: Ensure that job DSNTIJRT successfully created the stored procedures that provide server support for the Unified Debugger. an anonymous block) to process it, as described here. @strXML ntext. where department_id = par_deptno; end; Open in the database (or file), and hit the RUN button. Go to "Oracle SQL Developer" - "Tools" - "Preferences" - > Third Party JDBC Driver. – Clockwork-Muse. 1. If you see the status: success, it shows that the connection is fine. 3. $ db2look -d BP -e -cor -nofed -z PAUL | grep -A 999 'DDL Statements for Stored Procedures'. SQL stored procedures are supported by the DB2 Development Center tooling, providing an environment to code, test, and debug modules from your connected Basically "SQLCODE=-440, SQLSTATE=42884" means that stored procedure can not be found. I have used SQL developer before with Oracle. Right-click the stored procedure that you want to run and select Execute Stored Procedure. Parameters can be supplied to the above SP if they are Feb 2, 2021 · It appears that your issue is caused by Squirrel-SQL configuration somehow. procedures. Procedures in SQL provide the same benefits as procedures in a host language. Oct 25, 2021 · Like we can create a Stored procedure for Insert, select, update in SQL database. It appears that DataGrip version 2019. Stored procedure running a SQL runs very slow. The Run PL/SQL dialog window appears. Here's an example: DECLARE @myId int; DECLARE @myName nvarchar(60); DECLARE myCursor CURSOR FORWARD_ONLY FOR. We can also pass parameters to the Stored procedures. Aug 4, 2010 · DB2 stores the system related tables under syscat schema. International Technical Support Organization SQL Procedures, Triggers, and Functions on IBM DB2 for i April 2016 SG24-8326-00 Dec 20, 2017 · I have a stored procedure and I need to call it several times with different sets of input. I'm trying to migrate a DB2 on cloud database to oracle. thatjeffsmith. 16. FUNCTION or SPECIFIC FUNCTION. Deploy, debug, and run the stored procedure from the workbench. spsql identifies it as a stored procedure. PUT_LINE function to send diagnostic output from SQL routines to the console. Expand the database that you want, expand Programmability, and then expand Stored Procedures. --#SET TERMINATOR @. But i am facing some problems executing stored procedures. Also, in this case, the CALL statement fails. The linked server uses the IBM Drivers and not the Microsoft ones. Here's an example procedure that accepts XML as a string, and shreds it into tables. -9905 = The Session Manager could not be reached by a ping from the server. The root problem here: STRING_SPLIT doesn’t produce accurate estimates for the number of rows nor their contents. Using GUI Controls. Put_Line('Full name is: ' || FULL_NAME); END; 2. TABLE_A (var1, var2) VALUES (inputVar1, inputVar2);-- END; Oct 1, 2018 · I am trying to use the CLOB datatype as the output parameter in my stored procedure because its resultset exceeds the storage capacity of a var datatype. [db]. It also walks you through the process of debugging and running an NSP. Using SQL Worksheet. jar ), then you should configure squirrel to use the same db2jcc4. SELECT ROUTINE_DEFINITION FROM SYSIBM. 7 for Windows ? Example below is my dynamic generated stored procedure: CREATE PROCEDURE DB2ADMIN. This is same as "Run script output as a script" (you can see this button in the lower pane). I ended up composing a query based on the source of the SP. Documentation can be found here. Stored procedures are a powerful tool for increasing the Mar 26, 2014 · v_test_report_table test_report_table := test_report_table(); SELECT test_rec(sample1,sample2,sample3) bulk collect into v_test_report_table. Why are you using db2jcc. 7 with a db2 stored procedure . spsql file in Db2 Developer Extension, you get some additional actions in the toolbar in the upper right corner of the view: From left to right, these actions are Deploy, Debug, and Run. Query: Step 7. PUT_LINE() to cause that text to appear on the console. Create a SQL Worksheet and write PL/SQL anonymous block like this and hit f5. Nov 25, 2015 · PL/SQL procedure successfully completed. You just have to build the proper command string. I tried assigning the resultset to the the CLOB variable using the INTO query as shown in the query. Nov 1, 2019 · Defining SP to DB2: This process gives a name, declares IN & OUT parameters, Sets the external Language etc. The STORED PROCEDURE generally contains SQLs and they can be called by application programs. 21. The table only exists during the session and does not impact the database performance. Sep 25, 2018 · 2. Set up the workbench environment for stored procedure development. DECLARE FULL_NAME Varchar2(50); BEGIN GET_FULL_NAME('Foo', 'Bar', FULL_NAME); Dbms_Output. Take my Full MySQL Course Here: https://bit. If it is not working, contact your system administrator. This also works using openQuery which is returning different data from another table. So help CREATE PROCEDURE statement (SQL - native procedure) The CREATE PROCEDURE statement defines an SQL procedure, or a version of a procedure, at the current server and specifies the source statements for the procedure. my stored procedure look like this. result the same. Fortunately there are many alternative free tools such as IBM Data Studio, DBeaver, DB-Visualizer, Squirrel-SQL etc, some of which are more friendly for Db2. TABLE, VIEW and other DDL mixed in the output. To insert a break point. The idea is that in your routine, you assign to a variable some text (example, the table name and its count), then call DBMS_OUTPUT. Obviously, you couldn't supply parameters for the view, but it'd be trivial to supply them as a join condition/as part of a WHERE clause. g. Right click on the procedure and select " Run ". Step 3. github. dbo. but if I call. We In general, the stored procedures need to be created so that they open cursors on desired result sets. test. Run the procedure by clicking on Run icon. Aug 6, 2013 · SQL0104N An unexpected token "procedure" was found following "display ". CREATE PROCEDURE us_customers () May 12, 2017 · So, the real answer to this question is that you must iterate through the results of a select statement via a "cursor" and call the procedure from within that loop. exe or the bash shell). procedures". You'd need to write some SQL PL code (e. Step 1: We creating a Database. Nov 30, 2020 · DB2 Database Big Data Analytics. Before you want to get data set returning, you must declare what type of data it will be. Provide the connection details including connection name, user, password, hostname port, and the database ( Books) that you want to connect. sql: set heading off set feedback off BEGIN AML. You can get the stored procedure text by doing a. answered Feb 16, 2022 at 15:27. May 12, 2017 · 1 Make a connection to a database. CREATE or replace PROCEDURE GetFormData( IN p_Reviewer varchar(20)) The Db2 command STOP PROCEDURE prevents Db2 from accepting SQL CALL statements for one or more stored procedures. Expand the Procedures. GET_DEFINITION (69,'''','''')') AT MyLinkedDB2Server. This tutorial should take approximately 60 minutes to finish. Query: Output: Step 2: To use the GeeksforGeeks database use the below command. This is very similar to the changes in DB2 for LUW V8. SET in_iterations = in_iterations - 1; INSERT INTO mytab (when, num) VALUES (v Sep 23, 2021 · In this article let us see how to execute SQL Server Stored Procedure in MSSQL. To run a stored procedure in SQL Server, you can: Run the EXECUTE or EXEC statement; Call the procedure from another stored Step 7. sqc file. Execute a Stored Procedure in SQL Server. 6k 6 41 132. Click the Test button to test the connection. For my case, I noticed that in java code, I have to put the schema name in front of the stored procedure name, e. Identifies the function on which the privilege is granted. The example below will compile for Db2-LUW, but there are other errors and problems in your code that you will find later with testing. Apr 20, 2023 · In Oracle SQL Developer, you can run this procedure in two ways. Let’s write a simple stored procedure that inserts the current timestamp and a random value into a table multiple times, depending on an argument. Search for JDBC, you will find the Third Party JDBC Drivers under Database. Mar 19, 2019 · CREATE OR REPLACE PROCEDURE inner LANGUAGE SQL DYNAMIC RESULT SETS 1 READS SQL DATA -- (1) BEGIN DECLARE c1 CURSOR WITH RETURN -- (2) FOR SELECT tabschema, tabname FROM syscat. I saw a very common cause is the argument doesn't match. Using myCompiler, you can run your code instantly from any device. If the VERSION clause is not specified, the procedure definition is replaced. jar. Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601. 2. DO_ACCOUNT_AML() ; COMMIT END; / And the leave that batch script to handle only the control flow: myscript. Query: Step 2: Using Database. For example, SQL Server. I have downloaded SQL developer, added the correct JARs for the DB2 drivers and can run the migration successfully on the database. 2) This procedure actually appears amenable to being created as a view, which would likely be better able to use the optimizer in cases where you're joining to the results. IT is working stored procedure. Sep 13, 2016 · Here is an tested example which works on Squirrel 3. qcmdexec('CHGOBJOWN OBJ(XX/TBL1) OBJTYPE(*FILE) NEWOWN(GRPFRIENDS)'); There are a whole list of services. Creating a stored procedure in database connection: MySQL. Aug 2, 2019 · There is a DB2 service that allows you to execute commands. See full list on ibm. Is there a way to execute a stored procedure with an instruction to regenerate execution plan at Nov 7, 2014 · 1. change statement separator in squirrel > session > session properties > SQL : @ Nov 2, 2017 · I am not sure how to run sybase stored procedures from SQL Developer. The stored procedures can be run in sql developer tool using the below syntax. tables; OPEN c1; END Footnotes: The READS SQL DATA here is important; a table function cannot have side effects, and so shouldn't the stored procedure it calls. Expand Procedures Launch SQL Developer and follow these steps: Step 1. The stored procedures that this job Apr 20, 2012 · To enable DB2 in SQL Developer, you need to pull out the db2jcc. median_result_set (?) Jan 27, 2023 · Copy the SQL statement, paste it into the “SQL Commander” tab, and update the query accordingly. The trick is to passe with an transitional stored procedure MY_PROC_TEST to call the real stored procedure PROC_TEST. Nov 15, 2018 · As per comments, fix the syntax errors in your code. QUERY. So, we will create a database first: Step 1: Creating Database. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT. This article covers the basics of using Db2 Developer Extension to create a simple stored procedure and then explains some of the more complex things you might need to do when you create a stored procedure, such as working with DBCS data and user-defined types. The procedure should look like this: IN shipNo VARCHAR(30) ) DECLARE likeShipNo VARCHAR(32); SET likeShipNo = '%' || shipNo Feb 13, 2020 · Because of the original low 388 row estimate, SQL Server didn’t budget enough memory for the sort, which ends up spilling to disk. 2. – mustaccio. Description of this image Jun 18, 2012 · 3. In SQL Developer client tool: Go to connections on the left pane. We create stored procedures using the CREATE PROCEDURE command followed by SQL commands. 2 Right-click the stored procedure, user-defined function, or trigger you want to work with and choose Open from the shortcut menu. db2, containing Jun 18, 2019 · 1. Looking at this question, I ran this debug code to check if the users are the same, and it does not appear to be the same Jan 14, 2021 · Add a comment. Assuming you already have a connection configured in SQL Developer: from the View menu, select DBMS Output. CREATE or replace PROCEDURE GetFormData( IN p_Reviewer varchar(20)) Mar 24, 2023 · My SQL for creating the stored procedure is: RESULT SETS 1. When you open a . 19 (it mostly works for many versions) After adding that jar to third party JDBC Drivers. jar instead of db2jcc4. May 28, 2014 at 11:33. Query: Step 3: Create a table. Then, click the run button to create your stored procedure. WHERE Country = 'USA'; PostgreSQL. SELECT Id, Name FROM SomeTable; May 22, 2013 · How to create a Dynamic Stored Procedure using a Stored Procedure in IBM DB2 9. there you need to declare/create what types of data will come out. Aug 9, 2017 · Below is an example of your procedure if you submit it from the shell command line on Unix or Windows (db2cmd. Right-click the line of SQL syntax in the editor window where you want to insert a breakpoints. How do I then use the variable in a SQL statement, for example: SELECT * from vSchema. LANGUAGE SQL. after i selecting the list of currently available stored procedure and suppling the parameters. [schema]. Click the Save button to save the connection and then click Run your SQL code without installing anything. Sep 20, 2016 · I tried to Declare a stored procedure in powerbuilder. Sep 18, 2017 · I want to create a simple Oracle Stored procedure on SQL Developer that will return some records on a simple select query. Create an SQL stored procedure. Ken Flerlage (Member) 3 years ago. Browse to the location that you store the JDBC driver file, in this case, it is db2jcc. Point to Break point and then click Insert Break point. Also, when you return a cursor, you need to add the sentence RESULT SET 1 before begin and add the WITH RETURN TO CLIENT to the cursor that you want to return. It seems stored procedure uses a different path. Mar 11, 2019 · 0. Dec 6, 2009 · I am using oracle 10g express edition. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it Oct 27, 2021 · So, if I execute StoredProcedure2 from Remote1 using [Remote2]. Syntax: For creating a stored procedure. FROM Customers. If the calling application on the client workstation gets interrupted by a Control-C , then the client app may choose to stop, but any already running stored-procedure started by that app will continue running (by default) and be unaware that the client END. Choose Tools > Preferences… menu item: Step 2. I get a lot of data I cannot really understand. Top Rated Answers. It works perfectly fine in PL/SQL devloper's "tes" window, but I would like to see it work in the ORACLE SQL Developer proprietary stored procedure languages of several competitive databases, which assists in migrating and porting to DB2 for z/OS. Mar 19, 2020 · For example: highlight CALL customer_p(100) (for example) and when selected use Ctrl-F5 to run it. The only part of the migration I'm struggling with is the conversion of the stored procedures which are not outputted in the ddl. Example: Invoking Stored Procedures: In mainframes environment, A stored procedure can be invoked from an application program USING A CALL or it can be invoked from the Command Line. Oct 29, 2011 · One defined as CREATE PROCEDURE GetDeptInfo After days of researching I found how to write and run dynamic SQL on DB2: Execute SQL Statement stored in table Jul 5, 2013 · 5. The variables in DB2 doesn't need the @ like in MSSQL. You can qualify stored procedure names with a schema name. Lookup the syntax for all that is different from your versions and adjust it as needed. This command does not prevent CALL statements from running if they have already been queued or scheduled by Db2. [StoredProcedure] will this StoredProcudure2 really execute and process on Remote2 or Remote1? Or if I create the same stored procedure like StoredProcedure2 on Remote1 and update the records of Remote2 from Remote1, will it take the same time to run? Nov 7, 2014 · 1. ROUTINES; Alternately, you can choose to retrieve only the SPs in your schema by doing a: SELECT ROUTINE_DEFINITION FROM SYSIBM. So a query on syscat. In this case, you use the -f option flag (see Table 1) on the db2 command to specify the name of the command script. Db2 implicitly issues the command STOP PROCEDURE ACTION This stored procedure is created during Db2 subsystem installation and is invoked to check the connection between the debug client and server. UPDATED question: The core of my problem is: The stored procedure I (User1) created is not able to select from the some specific table (table1 created by another user (User2)) due to: SET r_count = (SELECT COUNT(*) FROM TEST_SCHEMA. pq gx nx fz cz cq uh hd ox xb