executing an AS400 CL command from a windows program 1

[ddiamond]

Any one know how I can execute an AS400 DB2 CL command from an external program that is not running on the AS400? Is it possible to do it using ODBC, ADO, or JDBC? If not, is it possible to wrap the CL command in an AS400 stored procedure?

For example: CLRPFM FILE(ADMSEARCH/ADDRKEYTMP) ;

This command is equivelent to an sql bulk delete statement without the transaction logging, so it is very fast.

Another useful CL command:
CPYF FROMFILE(ADMSEARCH/NAMEKEYTMP) TOFILE(ADMSEARCH/NAMEKEY) MBROPT(*ADD) ;

This is instead of an sql bulk insert into statement without the transaction logging.

 

[fredericofonseca]

It is possible, depending on what software you got installed on your PC.

One way is to use ADO, and execute a stored procedure on the AS400 that will in turn execute your command.

Another way, if you have iSeries express installed on your PC, is to use the remote command executable supplied with it. Dont remember its name now.

Another way, if the security on the AS400 allows it, is to issue a FTP QUOTE command, which allows you to execute a AS400 command also.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

Adding to my previous post. A AS400 Stored Procedure CAN be a CL or any other type of AS400 executable (apart from MI ones).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ddiamond]

Sounds like creating a stored procedure to execute the CL command is the easiest approach. I'm not to sure how to create a stored procedure that executes a CL command, but I found the following example on the web:

CREATE PROCEDURE mylib/exccmd              
(IN cmd CHAR (32000), IN len DEC (15,5)) 
LANGUAGE CL                                 
NOT DETERMINISTIC                           
NO SQL                                      
EXTERNAL NAME QSYS/QCMDEXC                  
PARAMETER STYLE GENERAL

call exccmd ('wrkoutq garbage',15)

I'll try it out tomorrow and let you know if it worked.

 

[fredericofonseca]

Yes that would be a way.

now be aware of all security issues with it, and also consider tha fact that for some commands you wish to execute on the as400 you may need to supply a library list for them to work as YOU wish or even to perform other stuff. On this situation you may need to do a bit more work, and create your own CL.

Its easy once you have done your first one.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ddiamond]

It does not appear that we have a qcmdexec api in qsys or any other library. Does that come as part of the operating system (v5r4) or does it need to be installed separately. I've done a number of internet searches on it, but none of them indicate that it should need to be installed.

 

[fredericofonseca]

that program can be a CL program you yourself create. as I said, ANY CL program can be an Stored Procedure.

try and create a similar sp as the one you have, just give it a diff name if you wish, and use a external name of your desire.

On that CL program, just execute directly a clrpmf of the library/file you supply as a parameter, and you will see it works.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ddiamond]

I finally got it working.

CREATE PROCEDURE ADMSEARCH.EXCCMD
(IN cmd CHAR (32000), IN len DEC (15,5))
LANGUAGE CL
NOT DETERMINISTIC
NO SQL
EXTERNAL NAME QSYS.QCMDEXC
PARAMETER STYLE GENERAL

CREATE PROCEDURE ADMSEARCH.RUN_CL_CMD ( 
  IN P_CMD VARCHAR(32000) ) 
  LANGUAGE SQL 
  SPECIFIC ADMSEARCH.RUN_CL_CMD 
  NOT DETERMINISTIC 
  MODIFIES SQL DATA 
  CALLED ON NULL INPUT 

  BEGIN ATOMIC 
    DECLARE V_CMD CHAR(32000) ;
    DECLARE V_LEN DEC (15,5) ;
    
    SET V_LEN = LENGTH(TRIM(P_CMD)) ;
    SET V_CMD = TRIM(P_CMD) ;
    
    CALL ADMSEARCH.EXCCMD(V_CMD, V_LEN) ;
  END  ;

And finally, I used ADO to execute the stored proc:

conn.execute "CALL ADMSEARCH.RUN_CL_CMD('CLRPFM FILE(ADMSEARCH/NAMEKEY)')"