CODEWITHSUNDEEP
db2db2-400
scott.se
scott.se

Reputation: 70

How can you call programs from a stored procedure?

I have followed other examples on stack exchange to call a built-in program from a stored procedure, but continue to get an error.

Working off this example (Looking for a working example of any OS/400 API wrapped in an external SQL stored procedure wrapped in a user defined SQL function) I built the following to attempt to create a wrapper command to allow me to change object security (my issue is that objects I create, regardless of library) are not always accessible to others in my some function, I must manually set to a common security group.

CREATE OR REPLACE PROCEDURE XX.TST( IN XOBJ CHAR(32), IN XOBJTYPE CHAR(10), IN XNEWOWN CHAR(10))

LANGUAGE CL

SPECIFIC XX.TST

NOT DETERMINISTIC

NO SQL

CALLED ON NULL INPUT

EXTERNAL NAME 'QSYS/CHGOBJOWN'

PARAMETER STYLE GENERAL;

CALL XX . TST('XX/TBL1','*FILE','GRPFRIENDS');

I get the following error:

External program CHGOBJOWN in QSYS not found

But have confirmed that going to the CL of the terminal emulator and typing QSYS/CHGOBJOWN takes me into the parameter input screen

Upvotes: 0

Views: 954

Answers (1)

jmarkmurphy
jmarkmurphy

Reputation: 11493

You are trying to define a command as a program, and that just won't work. A command object (*CMD) and a program object (*PGM) are two different things, and cannot be invoked the same way. All is not lost though. There is a DB2 service that allows you to execute commands. You just have to build the proper command string.

Instead of defining a stored procedure, you can call the existing DB2 service like this:

call qsys2.qcmdexec('CHGOBJOWN OBJ(XX/TBL1) OBJTYPE(*FILE) NEWOWN(GRPFRIENDS)');

There are a whole list of services. Documentation can be found here.

Upvotes: 3

Related Questions