Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Determine environment within a stored procedure?
has no ratings.
Published: 06 Feb 2015
Revised: 09 Feb 2015 - 1038 days ago
Last viewed on: 13 Dec 2017 (1964 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Determine environment within a stored procedure? Published by: Chris Proctor on 06 Feb 2015 view comments(9)

Happy Friday, all! I've been asked to modify one of our SQL stored procedures to perform some different logic based on environment (development,production). Is there a way of retrieving some value into a variable that would give me an ideaof which environment I'm in? A library from the libl, or something???

Thanks in advance for any suggestions! Have a great weekend!

Chris

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 7 days 3 hours 30 minutes ago

What's it written in? If RPG, I'd normally use a data area and test if it exists or not and if it does then you're in Development, if not, you're in production.  CL can do this as well.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 7 days 3 hours 24 minutes ago

Hi Bob. It's actually an SQL sproc.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 7 days 2 hours 11 minutes ago

Or perhaps even retrieving a sysval like QSRLNBR to determine if the sproc is being executed on the development box????

 

Posted by: GFuste
Premium member *
Jacksonville, FL
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 7 days 28 minutes ago

You might try retrieving the library list and then checking if the production library is positionally ahead of the test library if it's even there.  I've done that before.  Of course it may not be that simple but it might work.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 6 days 23 hours 31 minutes ago

I've actually created a CL program to get the SYSNAM value and made it into a stored procedure and tried calling it from within my stored procedure, but it's coming back with garbage rather than the SYSNAM value. Not sure what the problem is there, it's very simple. Here's what I have:

This sproc calls a simple CL program that just performs a RTVNETA command: 

CREATE PROCEDURE RetrieveSystemName

(

out p_sysnam char(8) -- system name

)

external name spsysnamc

language   CL

parameter style SQL

 

The sproc that calls it has the following logic related to the retrieval of the system name:

Declare v_sysnam char(8);

 

-- retrieve system name

 call RetrieveSystemName(v_sysnam);

That's all there is to it. If someone knows what I'm doing wrong I'd sure appreciate the input! :-)

Thanks!

Posted by: starbuck5250
Premium member *
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 6 days 22 hours 45 minutes ago
Edited: Fri, 06 Feb, 2015 at 16:10:12 (1041 days ago)

What does the CL look like?  Especially the parameter list.  PARAMETER STYLE SQL wants additional fields including the null indicator map for the input and output fields, sqlstate *char(5), function *char(517), specific *char(128) errormsg *char(70) and calltype *int(10).  Given the purpose of this, you're probably better served changing the SQL CREATE PROCEDURE to PARAMETER STYLE GENERAL.

Having said all that, I think you're better off passing the 'do some action' flag as a separate parameter.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 6 days 22 hours 42 minutes ago
Edited: Fri, 06 Feb, 2015 at 16:18:48 (1041 days ago)

This is the CLLE code:

PGM PARM(&SYSNAM)

DCL VAR(&SYSNAM) TYPE(*CHAR) LEN(8)

 RTVNETA SYSNAME(&SYSNAM)

 ENDPGM

I changed the parameter style to GENERAL and still nothing is returned. yell Lol

Thanks for the input.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 6 days 22 hours 22 minutes ago

Got it! I had to initialize the variable in the calling sproc and change the parm in the called sproc to inout. That did it! Thanks, everyone, for the input.

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Determine environment within a stored procedure?
Posted: 2 years 10 months 4 days 1 hours 34 minutes ago

Here's another option from within the SQL that is sometimes useful:

SELECT   SYSTEM_USER    AS USER_GROUP
        ,SESSION_USER   AS USER_ID
        ,CURRENT SERVER AS SERVER
        ,CURRENT SCHEMA AS CUR_LIB
        ,CURRENT PATH   AS LIB_LIST
FROM     SYSIBM.SYSDUMMY1

CURRENT SERVER is your IBM i serial number. 
CURRENT SCHEMA is the CURLIB from your library list.

CURRENT PATH revtrieves the library list as a string: "QSYS","QGPL","SYSPROC","STEVECCNJ"