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 - 982 days ago
Last viewed on: 17 Oct 2017 (1877 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 8 months 12 days 11 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 8 months 12 days 5 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 8 months 11 days 22 hours 52 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 8 months 11 days 21 hours 9 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 8 months 11 days 20 hours 13 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 8 months 11 days 19 hours 26 minutes ago
Edited: Fri, 06 Feb, 2015 at 16:10:12 (985 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 8 months 11 days 19 hours 24 minutes ago
Edited: Fri, 06 Feb, 2015 at 16:18:48 (985 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 8 months 11 days 19 hours 4 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 8 months 8 days 22 hours 16 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"