Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2012
    Posts
    37

    Unanswered: DB2 Not fenced concerns

    On another thread, it was said that running stored procedures with "not fenced" would speed up the stored procedure. I was able to confirm this with a stored procedure (customers database) that took 20-40min. Now it is finishing in under a second.

    It was also said that, if there was a storage violation, db2 could crash.

    what is the percentage of this happening, what would affect this percentage?

    Are there any other things we can do, that do not risk crashing the system?

    Code:
    CREATE OR REPLACe PROCEDURE STSIQRY_PCMSDA_FILINGCASES(
      @iCSCPCODE varchar(30),
      @iSearchFlag varchar(30))
      LANGUAGE SQL
      READS SQL DATA
      RESULT SETS 1
      NOT FENCED
    begin
    
        declare C1 scroll cursor with return for
    select a.lastname
         , a.frstname
         , a.midname
         , a.CITATION
         , CHAR( DATE( NULLIF(a.CPDOB   , '1/1/0001') ) , USA ) CPDOB
         , CHAR( DATE( NULLIF(a.CSARRDT , '1/1/0001') ) , USA ) CSARRDT
         , b.warantno
         , b.casenbr
         , b.appcode
         , b.cmscase
         , b.PROSCODE
         , p.ProsLast
         , IFNULL(p.ProsFrst , '') ProsFrst
         , IFNULL(p.ProsMid  , '') ProsMid 
         , IFNULL(c.CPChgDsc , '') CPChgDsc
    
     from  CMCPTYP a
     INNER JOIN 
           CMCASEP b
       ON  a.appcode = b.appcode
       and a.cmscase = b.cmscase
     left  outer join
           PTPROSP  p 
       on  b.PROSCODE = p.PROSCODE
     INNER JOIN
           CMCPCHP c
       on  b.appcode = c.appcode
       and b.cmscase = c.cmscase
       AND c.PM46gA = 'Y'
     WHERE a.CSCPCODE = @iCSCPCODE
       and a.cptypint = 'DF'
       and A.appcode in (
           select appcode
            from  CMAAGYP
            where APPTYPE = 'P'
           )
    	  and a.appcode = b.appcode and a.cmscase = b.cmscase
       AND exists (
           select 1
            from  CMLIDXP c
            left  outer join
                  cmliexp d
              on  c.appcode  = d.appcode
              and c.cmscase  = d.cmscase
              and c.casptysq = d.casptysq
              and c.ltrseq   = d.ltrseq 
            where
              (       @iSearchFlag =  'WEB'
                  AND IFNULL(d.lieflg2 , 'N') = 'N'
               OR     @iSearchFlag <> 'WEB'
                  AND d.liamt1  = 0
              )
              and c.stscde = 'RFD'
              and a.appcode = c.appcode
              and
              (   a.cmscase = c.cmscase
               or c.cmscase = (
                  select mstcseq
                   from  CMMCRCP d
                   where a.appcode = d.appcode
                     and a.cmscase = d.cmscase
                  ) /* c.cmscase = ( */
              )
           ) /* exists ( */
    fetch first 5000 rows only
    for read only optimize for 5000 rows;
    
    OPEN C1;
    set result sets cursor C1;
    end

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SQL SP's are not fenced by definition (at least that is what I thought).
    C or C++ SP's can be fenced or not fenced.
    Java SP's are fenced by definition.

    The reason that SQL SP's are not fenced is that the executable code (and memory addressing) is handled by the DB2 code that is used for running SQL SP's, and like the rest of DB2 the development lab makes sure the DB2 code does not violate any memory addressing rules (or least when they do it, it is very rare and quickly fixed).

    Any C programmer can knowingly or unknowingly very easily violate member and start writing on some other programs memory, so that is why they leave it up to you to decide how well it is tested.

    My understanding is that Java must be fenced for different reasons, but I don't recall what those reasons are.

    I am not sure why you included the SQL SP in your post. Did you get it to run as fenced? I was not aware you could even do that since it is written in the SQL language, but there is no reason to run it as fenced since there are no memory addressng commands in SQL that a programmer can mess up.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2012
    Posts
    37
    Yes, I was able to get it to run fenced, however, like I mentioned, it took 20-40 min just to finish. I then added the "Not Fenced" and it took less a second to finish.

    I posted it because I was hoping an alternative could be given to running it in "Fenced" and making it run faster or just as fast as the "Not Fenced".



    Edit:

    This is the only script having issues right now. All the scripts have been converted from sql to db2, and most work so far. This one works, it is just slow.

    Code:
    CREATE OR REPLACe PROCEDURE STSIQRY_PCMSDA_FILINGCASES(
      @iCSCPCODE varchar(30),
      @iSearchFlag varchar(30))
      LANGUAGE SQL
      READS SQL DATA
      RESULT SETS 1
      FENCED
    begin
    The above behaves just like the below ...... about 20-40 min from now, the stored procedure will finish.
    Code:
    CREATE OR REPLACe PROCEDURE STSIQRY_PCMSDA_FILINGCASES(
      @iCSCPCODE varchar(30),
      @iSearchFlag varchar(30))
      LANGUAGE SQL
      READS SQL DATA
      RESULT SETS 1
    begin

    Doing the below, runs under a second.
    Code:
    CREATE OR REPLACe PROCEDURE STSIQRY_PCMSDA_FILINGCASES(
      @iCSCPCODE varchar(30),
      @iSearchFlag varchar(30))
      LANGUAGE SQL
      READS SQL DATA
      RESULT SETS 1
      Not fenced
    begin
    Last edited by zaryk; 02-19-12 at 17:01.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by zaryk View Post
    Yes, I was able to get it to run fenced, however, like I mentioned, it took 20-40 min just to finish. I then added the "Not Fenced" and it took less a second to finish.

    I posted it because I was hoping an alternative could be given to running it in "Fenced" and making it run faster or just as fast as the "Not Fenced".
    SQL storeed procedures are not designed to run fenced, and for reasons I explained, there is no reason to force them to run as fenced. You have way too much free time on your hands.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2012
    Posts
    37
    I am working on a sunday afternoon, of course, I have too much free time on my hands.

    Tell me this, if stored procedures aren't designed to run "Fenced", then why is it that both our as400 database and the customers as400 database defaulted to running stored procedures as "Fenced". Are there options within as400 to changes this?

    I am just trying to get an understanding. Only as400 experience I have is the 2-3 weeks it took to convert 18 stored procedures, and the 1-2 weeks it took to debug them running them with our c# application.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by zaryk View Post
    both our as400 database and the customers as400 database
    You should have mentioned this in the beginning.

  7. #7
    Join Date
    Jan 2012
    Posts
    37
    And yet, what it defaults to was not my question, and from my point of view does not matter, because I did mention that both "Fenced" and "Not Fenced" have been ran, it is just a matter of performance.

    So, if "You should have mentioned this in the beginning." is all your going to say, I would like to you stay off this thread or tell me something useful.

    If there are options within as400 to change this, or if there is no concern with running stored procedures as "Not Fenced" then tell me, and I will gladly tell you "Thank you" and go about my business.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The reason he said you should have mentioned it is that DB2 for iSeries is completely different under the covers from DB2 LUW. The number of questions about DB2 iSeries on this forum is relatively small, and there are not many knowledgeable responses to such questions.

    It may be that DB2 iSeries SQL Stored Procedures generate C code, as was once the case for DB2 LUW (in V8, which is no longer supported), so it can be run as fenced. But assuming there is not a bug in DB2 iSeries that generates the C code, I am not sure why you would want to do that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2012
    Posts
    37
    But still, what you just said there was so much more useful than "You should have mentioned this in the beginning. "

    Thanks You. Is it safe to continue using "Not Fenced"? And what would you do or any other, if in this situation?

    Based on what you are telling me, 99% of the time it should be fine to continue using "Not Fenced", or at least that is what I am understanding.
    Last edited by zaryk; 02-19-12 at 20:32.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by zaryk View Post
    I would like to you stay off this thread or tell me something useful
    You should use these golden words as your Signature in the forum
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by zaryk View Post
    Based on what you are telling me, 99% of the time it should be fine to continue using "Not Fenced", or at least that is what I am understanding.
    I am not a DB2 for iSeries expert, but if the SP is written in SQL Language, then not fenced is probably safe, especially if you test it. If you write your own SP in C or Java, that would be a different situation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jan 2012
    Posts
    37
    Thanks. I will pass this information along to my manager. Farewell, and all a good night.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  13. #13
    Join Date
    Nov 2011
    Posts
    334
    ”Yes, I was able to get it to run fenced, however, like I mentioned, it took 20-40 min just to finish. I then added the "Not Fenced" and it took less a second to finish.“
    Are you sure , I think "fenced" and "no fenced" mode would not make so big difference on perfomance....

  14. #14
    Join Date
    Jan 2012
    Posts
    37
    No, I am not sure. I dreamt it. I didn't add "Not Fenced" to the stored procedure, and it didn't take a secod to run. And I didn't change "Not Fenced" to "Fenced" and it didn't take 20-40 min to run.

    Seriously, that was a realistic dream. Let me go try it now that I am awake.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by fengsun2 View Post
    ”Yes, I was able to get it to run fenced, however, like I mentioned, it took 20-40 min just to finish. I then added the "Not Fenced" and it took less a second to finish.“
    Are you sure , I think "fenced" and "no fenced" mode would not make so big difference on perfomance....
    When it comes to DB2 iSeries, what one would normally think may not apply.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •