Loading ...
Sorry, an error occurred while loading the content.

Re: On the usefulness use of BIND variables (2)

Expand Messages
  • hlwang1128
    PSoft has made it a mission to not use bind variable to achieve platform-independence. So forget the bind variable in Psoft. H.W
    Message 1 of 6 , Mar 10, 2009
    • 0 Attachment
      PSoft has made it a mission to not use bind variable to achieve platform-independence.


      So forget the bind variable in Psoft.


      H.W

      --- In psftdba@yahoogroups.com, <Willem.Leenen@...> wrote:
      >
      > Of course, 5 minutes after I asked the question in a forum, I stumbled
      > upon a blogfeed that answered this question:
      >
      >
      >
      > http://www.feedage.com/feeds/1954427/the-peoplesoft-dba-blog
      >
      >
      >
      > Kind Regards,
      >
      > Willem
      >
      > -----------------------------------------------------------------
      > ATTENTION:
      > The information in this electronic mail message is private and
      > confidential, and only intended for the addressee. Should you
      > receive this message by mistake, you are hereby notified that
      > any disclosure, reproduction, distribution or use of this
      > message is strictly prohibited. Please inform the sender by
      > reply transmission and delete the message without copying or
      > opening it.
      >
      > Messages and attachments are scanned for all viruses known.
      > If this message contains password-protected attachments, the
      > files have NOT been scanned for viruses by the ING mail domain.
      > Always scan attachments before opening them.
      > -----------------------------------------------------------------
      >
    • David Kurtz
      I don t think that is a fair assesment at all. PeopleSoft certainly pursued a policy of platform independence, but I don t see how that relates to bind
      Message 2 of 6 , Mar 10, 2009
      • 0 Attachment
        I don't think that is a fair assesment at all. PeopleSoft certainly pursued
        a policy of platform independence, but I don't see how that relates to bind
        variables.

        It is true that on some platforms (eg Sybase) all bind variables were
        resolved to literals, but that is certainly not the case on Oracle. Bind
        variables are used throughout PeopleTools. However, were dynamic SQL is
        used the literal values of variables are concatenated into the strings that
        are built up.

        In Application Engine, where this thread started, it is true that by default
        the bind variables become litterals by the time the SQL hits the database.
        However, you can easily prevent that with the ReUse Statement flag.

        (see
        http://blog.psftdba.com/2009/02/performance-benefits-of-reuse-statement_27.h
        tml)

        What is a pity is that
        i) The default value for the ReUse Statement flag is false (but there are
        limitations on its use, you cannot just turn it on for all statements, and
        PeopleSoft had to consider legacy programs when they introduced it).
        ii) The PeopleSoft did not make more use of the ReUse Statement flag in
        their own batches.

        regards
        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david.kurtz@...
        web: www.go-faster.co.uk
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
        http://blog.go-faster.co.uk
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

        >-----Original Message-----
        >From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]
        >On Behalf Of hlwang1128
        >Sent: Tuesday, March 10, 2009 1:03 PM
        >To: psftdba@yahoogroups.com
        >Subject: PeopleSoft DBA Forum Re: On the usefulness use of
        >BIND variables (2)
        >
        >PSoft has made it a mission to not use bind variable to
        >achieve platform-independence.
        >
        >
        >So forget the bind variable in Psoft.
        >
        >
        >H.W
        >
        > --- In psftdba@yahoogroups.com, <Willem.Leenen@...> wrote:
        >>
        >> Of course, 5 minutes after I asked the question in a forum,
        >I stumbled
        >> upon a blogfeed that answered this question:
        >>
        >>
        >>
        >> http://www.feedage.com/feeds/1954427/the-peoplesoft-dba-blog
        >>
        >>
        >>
        >> Kind Regards,
        >>
        >> Willem
        >>
        >> -----------------------------------------------------------------
        >> ATTENTION:
        >> The information in this electronic mail message is private and
        >> confidential, and only intended for the addressee. Should
        >you receive
        >> this message by mistake, you are hereby notified that any
        >disclosure,
        >> reproduction, distribution or use of this message is strictly
        >> prohibited. Please inform the sender by reply transmission
        >and delete
        >> the message without copying or opening it.
        >>
        >> Messages and attachments are scanned for all viruses known.
        >> If this message contains password-protected attachments, the files
        >> have NOT been scanned for viruses by the ING mail domain.
        >> Always scan attachments before opening them.
        >> -----------------------------------------------------------------
        >>
        >
        >
        >
        >
        >------------------------------------
        >
        >PeopleSoft for the Oracle DBA is published by Apress - see
        >http://www.psftdba.com.
        >The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.
        >Yahoo! Groups Links
        >
        >
        >
      • Hailin Wang
        Tip my hat to David first. I always read your response, even in threads outside my interest. However, is it not true that one of the methods Psoft adopted to
        Message 3 of 6 , Mar 10, 2009
        • 0 Attachment
          Tip my hat to David first. I always read your response, even in threads outside my interest.

          However, is it not true that one of the methods Psoft adopted to stay away platform specific code is having PeopleTool system table and data, which in turns generate a lot of Dynamic SQLs, ultimately leading to inevitablely poor usage of bind variables?

          Not to argue, but to reassure i got my understanding str8t.

          Thanks

          Nick


          --- On Tue, 3/10/09, David Kurtz <david.kurtz@...> wrote:

          From: David Kurtz <david.kurtz@...>
          Subject: RE: PeopleSoft DBA Forum Re: On the usefulness use of BIND variables (2)
          To: psftdba@yahoogroups.com
          Date: Tuesday, March 10, 2009, 6:41 PM

          I don't think that is a fair assesment at all. PeopleSoft certainly pursued
          a policy of platform independence, but I don't see how that relates to bind
          variables.

          It is true that on some platforms (eg Sybase) all bind variables were
          resolved to literals, but that is certainly not the case on Oracle. Bind
          variables are used throughout PeopleTools. However, were dynamic SQL is
          used the literal values of variables are concatenated into the strings that
          are built up.

          In Application Engine, where this thread started, it is true that by default
          the bind variables become litterals by the time the SQL hits the database.
          However, you can easily prevent that with the ReUse Statement flag.

          (see
          http://blog. psftdba.com/ 2009/02/performa nce-benefits- of-reuse- statement_ 27.h
          tml)

          What is a pity is that
          i) The default value for the ReUse Statement flag is false (but there are
          limitations on its use, you cannot just turn it on for all statements, and
          PeopleSoft had to consider legacy programs when they introduced it).
          ii) The PeopleSoft did not make more use of the ReUse Statement flag in
          their own batches.

          regards
          ____________ _________ ____
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          mailto:david.kurtz@ go-faster. co.uk
          web: www.go-faster. co.uk
          Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
          DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle:
          http://blog. go-faster. co.uk
          PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

          >-----Original Message-----
          >From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com]
          >On Behalf Of hlwang1128
          >Sent: Tuesday, March 10, 2009 1:03 PM
          >To: psftdba@yahoogroups .com
          >Subject: PeopleSoft DBA Forum Re: On the usefulness use of
          >BIND variables (2)
          >
          >PSoft has made it a mission to not use bind variable to
          >achieve platform-independen ce.
          >
          >
          >So forget the bind variable in Psoft.
          >
          >
          >H.W
          >
          > --- In psftdba@yahoogroups .com, <Willem.Leenen@ ...> wrote:
          >>
          >> Of course, 5 minutes after I asked the question in a forum,
          >I stumbled
          >> upon a blogfeed that answered this question:
          >>
          >>
          >>
          >> http://www.feedage. com/feeds/ 1954427/the- peoplesoft- dba-blog
          >>
          >>
          >>
          >> Kind Regards,
          >>
          >> Willem
          >>
          >> ------------ --------- --------- --------- --------- --------- -
          >> ATTENTION:
          >> The information in this electronic mail message is private and
          >> confidential, and only intended for the addressee. Should
          >you receive
          >> this message by mistake, you are hereby notified that any
          >disclosure,
          >> reproduction, distribution or use of this message is strictly
          >> prohibited. Please inform the sender by reply transmission
          >and delete
          >> the message without copying or opening it.
          >>
          >> Messages and attachments are scanned for all viruses known.
          >> If this message contains password-protected attachments, the files
          >> have NOT been scanned for viruses by the ING mail domain.
          >> Always scan attachments before opening them.
          >> ------------ --------- --------- --------- --------- --------- -
          >>
          >
          >
          >
          >
          >----------- --------- --------- -------
          >
          >PeopleSoft for the Oracle DBA is published by Apress - see
          >http://www.psftdba. com.
          >The PeopleSoft DBA Forum is managed by http://www.go- faster.co. uk.
          >Yahoo! Groups Links
          >
          >
          >


        • pratik banerjee
          Hi David,   I am inetersted in knowing more on the reuse flag that you mentioned. However the link that you have provided doesn t seems to be working.  
          Message 4 of 6 , Mar 10, 2009
          • 0 Attachment
            Hi David,
             
            I am inetersted in knowing more on the reuse flag that you mentioned. However the link that you have provided doesn't seems to be working.  
             
            Thanks
            Pratik

            --- On Wed, 3/11/09, David Kurtz <david.kurtz@...> wrote:
            From: David Kurtz <david.kurtz@...>
            Subject: RE: PeopleSoft DBA Forum Re: On the usefulness use of BIND variables (2)
            To: psftdba@yahoogroups.com
            Date: Wednesday, March 11, 2009, 4:11 AM

            I don't think that is a fair assesment at all. PeopleSoft certainly pursued
            a policy of platform independence, but I don't see how that relates to bind
            variables.

            It is true that on some platforms (eg Sybase) all bind variables were
            resolved to literals, but that is certainly not the case on Oracle. Bind
            variables are used throughout PeopleTools. However, were dynamic SQL is
            used the literal values of variables are concatenated into the strings that
            are built up.

            In Application Engine, where this thread started, it is true that by default
            the bind variables become litterals by the time the SQL hits the database.
            However, you can easily prevent that with the ReUse Statement flag.

            (see
            http://blog. psftdba.com/ 2009/02/performa nce-benefits- of-reuse- statement_ 27.h
            tml)

            What is a pity is that
            i) The default value for the ReUse Statement flag is false (but there are
            limitations on its use, you cannot just turn it on for all statements, and
            PeopleSoft had to consider legacy programs when they introduced it).
            ii) The PeopleSoft did not make more use of the ReUse Statement flag in
            their own batches.

            regards
            ____________ _________ ____
            David Kurtz
            Go-Faster Consultancy Ltd.
            tel: +44 (0)7771 760660
            fax: +44 (0)7092 348865
            mailto:david.kurtz@ go-faster. co.uk
            web: www.go-faster. co.uk
            Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
            DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle:
            http://blog. go-faster. co.uk
            PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

            >-----Original Message-----
            >From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com]
            >On Behalf Of hlwang1128
            >Sent: Tuesday, March 10, 2009 1:03 PM
            >To: psftdba@yahoogroups .com
            >Subject: PeopleSoft DBA Forum Re: On the usefulness use of
            >BIND variables (2)
            >
            >PSoft has made it a mission to not use bind variable to
            >achieve platform-independen ce.
            >
            >
            >So forget the bind variable in Psoft.
            >
            >
            >H.W
            >
            > --- In psftdba@yahoogroups .com, <Willem.Leenen@ ...> wrote:
            >>
            >> Of course, 5 minutes after I asked the question in a forum,
            >I stumbled
            >> upon a blogfeed that answered this question:
            >>
            >>
            >>
            >> http://www.feedage. com/feeds/ 1954427/the- peoplesoft- dba-blog
            >>
            >>
            >>
            >> Kind Regards,
            >>
            >> Willem
            >>
            >> ------------ --------- --------- --------- --------- --------- -
            >> ATTENTION:
            >> The information in this electronic mail message is private and
            >> confidential, and only intended for the addressee. Should
            >you receive
            >> this message by mistake, you are hereby notified that any
            >disclosure,
            >> reproduction, distribution or use of this message is strictly
            >> prohibited. Please inform the sender by reply transmission
            >and delete
            >> the message without copying or opening it.
            >>
            >> Messages and attachments are scanned for all viruses known.
            >> If this message contains password-protected attachments, the files
            >> have NOT been scanned for viruses by the ING mail domain.
            >> Always scan attachments before opening them.
            >> ------------ --------- --------- --------- --------- --------- -
            >>
            >
            >
            >
            >
            >----------- --------- --------- -------
            >
            >PeopleSoft for the Oracle DBA is published by Apress - see
            >http://www.psftdba. com.
            >The PeopleSoft DBA Forum is managed by http://www.go- faster.co. uk.
            >Yahoo! Groups Links
            >
            >
            >


          • David Kurtz
            Again I must disagree. Platform independence did not cause PeopleSoft to store code in the tools tables. Nor vice versa. PeopleSoft SQRs have been written in
            Message 5 of 6 , Mar 11, 2009
            • 0 Attachment
              Again I must disagree. 
               
              Platform independence did not cause PeopleSoft to store code in the tools tables.  Nor vice versa.  PeopleSoft SQRs have been written in a platform generic way, but they are stored as flat files.  You can write platform specific code in PeopleTools just as easily as platform generic code.  There are examples in PeopleCode of different SQL being issued on different platforms.  In AE you can make a step platform specific, so you can have different code on different platforms (although it is up to the developer to make sure it is functionally equivalent).  This is so PeopleSoft could deliver one program to all platforms. 
               
              Most of the metadata in the PeopleTools tables is used by the component processor, and that dynamically generates SQL which certainly does make extensive use of bind variables.  The code generated by the component processor is mostly platform generic, but there are also platform specific elements.  Eg. Platform specific date formatting functions are added dynamically depending on which database you are using.  The date formatting macros also generate different code on different platforms.
               
              The areas where you do get literal values in SQL statements are
              i) where PeopleCode is used to build SQL statements in strings that are submitted via SQLExec and either scroll or rowset functions.  Although even if you do this you can still use bind variables (if you know how many bind variables you will have.  It is not because PeopleSoft store application code in the Tools tables, it is partly because the PeopleCode language lends itself to this construction, and partly due to the coding style of the PeopleSoft development.
              ii) Application Engine - we talked the ReUse Statement flag earlier.
              iii) SQR - you can embed blocks of SQL in variables and put the variables into SQL statements, and AFAIK you can't put bind variables into these strings. SQR was originally a third party product licensed by PeopleSoft
               

              regards
              _________________________
              David Kurtz
              Go-Faster Consultancy Ltd.
              tel: +44 (0)7771 760660
              fax: +44 (0)7092 348865
              mailto:david.kurtz@...
              web: www.go-faster.co.uk
              Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
              DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
              PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

               


              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Hailin Wang
              Sent: Wednesday, March 11, 2009 1:27 AM
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Re: On the usefulness use of BIND variables (2)

              Tip my hat to David first. I always read your response, even in threads outside my interest.

              However, is it not true that one of the methods Psoft adopted to stay away platform specific code is having PeopleTool system table and data, which in turns generate a lot of Dynamic SQLs, ultimately leading to inevitablely poor usage of bind variables?

              Not to argue, but to reassure i got my understanding str8t.

              Thanks

              Nick


              --- On Tue, 3/10/09, David Kurtz <david.kurtz@...> wrote:

              From: David Kurtz <david.kurtz@...>
              Subject: RE: PeopleSoft DBA Forum Re: On the usefulness use of BIND variables (2)
              To: psftdba@yahoogroups.com
              Date: Tuesday, March 10, 2009, 6:41 PM

              I don't think that is a fair assesment at all. PeopleSoft certainly pursued
              a policy of platform independence, but I don't see how that relates to bind
              variables.

              It is true that on some platforms (eg Sybase) all bind variables were
              resolved to literals, but that is certainly not the case on Oracle. Bind
              variables are used throughout PeopleTools. However, were dynamic SQL is
              used the literal values of variables are concatenated into the strings that
              are built up.

              In Application Engine, where this thread started, it is true that by default
              the bind variables become litterals by the time the SQL hits the database.
              However, you can easily prevent that with the ReUse Statement flag.

              (see
              http://blog. psftdba.com/ 2009/02/performa nce-benefits- of-reuse- statement_ 27.h
              tml)

              What is a pity is that
              i) The default value for the ReUse Statement flag is false (but there are
              limitations on its use, you cannot just turn it on for all statements, and
              PeopleSoft had to consider legacy programs when they introduced it).
              ii) The PeopleSoft did not make more use of the ReUse Statement flag in
              their own batches.

              regards
              ____________ _________ ____
              David Kurtz
              Go-Faster Consultancy Ltd.
              tel: +44 (0)7771 760660
              fax: +44 (0)7092 348865
              mailto:david.kurtz@ go-faster. co.uk
              web: www.go-faster. co.uk
              Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
              DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle:
              http://blog. go-faster. co.uk
              PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

              >-----Original Message-----
              >From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com]
              >On Behalf Of hlwang1128
              >Sent: Tuesday, March 10, 2009 1:03 PM
              >To: psftdba@yahoogroups .com
              >Subject: PeopleSoft DBA Forum Re: On the usefulness use of
              >BIND variables (2)
              >
              >PSoft has made it a mission to not use bind variable to
              >achieve platform-independen ce.
              >
              >
              >So forget the bind variable in Psoft.
              >
              >
              >H.W
              >
              > --- In psftdba@yahoogroups .com, <Willem.Leenen@ ...> wrote:
              >>
              >> Of course, 5 minutes after I asked the question in a forum,
              >I stumbled
              >> upon a blogfeed that answered this question:
              >>
              >>
              >>
              >> http://www.feedage. com/feeds/ 1954427/the- peoplesoft- dba-blog
              >>
              >>
              >>
              >> Kind Regards,
              >>
              >> Willem
              >>
              >> ------------ --------- --------- --------- --------- --------- -
              >> ATTENTION:
              >> The information in this electronic mail message is private and
              >> confidential, and only intended for the addressee. Should
              >you receive
              >> this message by mistake, you are hereby notified that any
              >disclosure,
              >> reproduction, distribution or use of this message is strictly
              >> prohibited. Please inform the sender by reply transmission
              >and delete
              >> the message without copying or opening it.
              >>
              >> Messages and attachments are scanned for all viruses known.
              >> If this message contains password-protected attachments, the files
              >> have NOT been scanned for viruses by the ING mail domain.
              >> Always scan attachments before opening them.
              >> ------------ --------- --------- --------- --------- --------- -
              >>
              >
              >
              >
              >
              >----------- --------- --------- -------
              >
              >PeopleSoft for the Oracle DBA is published by Apress - see
              >http://www.psftdba. com.
              >The PeopleSoft DBA Forum is managed by http://www.go- faster.co. uk.
              >Yahoo! Groups Links
              >
              >
              >


            Your message has been successfully submitted and would be delivered to recipients shortly.