CODEWITHSUNDEEP
stored-procedurestriggersdb2
swayam swayam
swayam swayam

Reputation: 131

Export whole query to a file using stored procedure

Need help on getting query on a text file using stored procedure in db2. I'm very new to this so please help.

Till now i am able to create a file and get get data in created file. But i want a complete query in the file not a single column data.

I have created a Stored Procedure like this

 CREATE or replace PROCEDURE SCOPEMASTERP(my_statement varchar(10000))
AUTONOMOUS
BEGIN
  DECLARE v_filehandle    UTL_FILE.FILE_TYPE;
  DECLARE v_dirAlias      VARCHAR(50) DEFAULT 'mydir';
  DECLARE v_filename      VARCHAR(100) DEFAULT 'bluestar_transaction-';
  DECLARE v_format        VARCHAR(200);
  SET v_format = '%s\n';

  set v_filename = concat(v_filename, VARCHAR_FORMAT(current_date, 'DD-MM-YYYY'));
  set v_filename = concat(v_filename, '.log');
  CALL UTL_DIR.CREATE_OR_REPLACE_DIRECTORY('D:', '/archivelog/asd/');
  SET v_filehandle = UTL_FILE.FOPEN(v_dirAlias,v_filename,'a');
  CALL UTL_FILE.PUTF(v_filehandle,v_format, my_statement);
  CALL DBMS_OUTPUT.PUT_LINE('Wrote to file: ' || v_filename);
  CALL UTL_FILE.FCLOSE(v_filehandle);
END

and i have created a trigger inside trigger i am calling stored procedure

 CREATE OR REPLACE TRIGGER SCOPEMASTER_Trigger
     AFTER INSERT ON SERVEIT.SCOPENAMEMASTER
     REFERENCING NEW AS N
     FOR EACH ROW
     BEGIN ATOMIC

 call SCOPEMASTERP(N.SCOPENAMEID);
     END

insert statement i am executing 

 Insert into SERVEIT.SCOPENAMEMASTER (SCOPENAMEID) values (1013)
GO

And file which is creating in "D" drive i am getting

enter image description here

But instead of just getting 1013 i need the complete query in the file 

Insert into SERVEIT.SCOPENAMEMASTER (SCOPENAMEID) values (1013)

What changes i need to do please help. Thanks in advance!!

Upvotes: 0

Views: 245

Answers (1)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11032

There are no special registers/variables/etc. available in DB2 that provide the SQL statement that is being executed, so what you're asking for is not possible.

Upvotes: 1

Related Questions