User Profile

ricky · Jan 2 '14, 08:58 AM

I am using Db2 9.5 LUW. Is there a way in DB2 stored procedure can run a .sql file? Like the following:

Code:

sql file: abc.sql
insert into tbl values ('ABC','Very Long Text here');

SP: SP_TEST
CREATE PROCEDURE SP_TEST()
   language sql
   BEGIN
   --Read and run from C:\MyPath\abc.sql
END@

ricky · Dec 21 '12, 04:35 PM

Hi,

I have some procedures with dynamic sql(generic procedure for different tables) to do the convert columns to rows but have performance issue. Is there a better way like a single SQL that can solve my problem, thanks!!!

Table A(total 20 columns)
Col1 Col2 Col3 Col4 ... Col20

Table B(total 10 columns)
Col1 Col2 Col3 Col4... Col10

My procedure like this(pseudo code)...

ricky · Aug 17 '12, 09:00 AM

I have a run.bat and a sql file. For security reason, I can't explicit the Password in MySql.sql file, any solution can help?

1) run.bat
db2cmd /c /w db2 -tvf C:\MySql.sql -z result.log

2) MySql.sql
connect to DBname user UserID using Password;
call SP_TEST('This is Testing', ?);

Thanks!

ricky · Jul 31 '12, 03:50 AM

I have three SPs to test concurrency processing & row lock.

i. SP_X6, simply do insert a record into table test_lock values (i, 'N', current time, null, null) per 5 seconds.
Table structure:
(
Number int,
Status char(1),
Create_time timestamp,
SP1_time timestamp,
SP2_time timestamp
)

ii. SP_LOOP_X4() & SP_LOOP_X5() which simply loop and call SP_X4,...

User Profile

Profile Sidebar

Can DB2 stored procedure run a .sql file?

How to convert columns to rows

.bat file call DB2 stored procedure, without the connection string?

DB2 process concurrency/ lock question