memory needs of SQL based system - IBM AS400

This is a discussion on memory needs of SQL based system - IBM AS400 ; We are in the process of rewriting a major portion of our code to switch from record level RPG access methods to using SQL procedures and embedded SQL. Our 520 now has 4gb of memory. WIll we see performance issues ...

+ Reply to Thread
Results 1 to 9 of 9

Thread: memory needs of SQL based system

  1. memory needs of SQL based system

    We are in the process of rewriting a major portion of our code to
    switch from record level RPG access methods to using SQL procedures and
    embedded SQL.

    Our 520 now has 4gb of memory.

    WIll we see performance issues arise as we implement the new SQL driven
    access methods and procedures? Any gotchas we need to be aware of so we
    don't unleash an embedded SQL that locks up the system?

    Would it be advisable up front to increase our RAM to say 16gb?

    A brave new world for us old record level access fogies.....


  2. Re: memory needs of SQL based system

    When running in SQL intensive environment, rule of thumb is 8GB of main
    memory per processor.

    You'll want to have good indexing strategy in place, in form of keyed
    LFs and preferrably SQL indexes. Make sure you're current on DB group
    PTFs.
    If you can, take a query optimization class with IBM.

    Don't expect same performance for record-at-a-time type access. SQL is
    designed for set-at-a-time access.

    If I was in your situation, I'd start to roll things out slowly, but
    sounds like you guys are just going to bite the bullet.

    Good luck.

    Oldest Fossil Alive wrote:
    > We are in the process of rewriting a major portion of our code to
    > switch from record level RPG access methods to using SQL procedures and
    > embedded SQL.
    >
    > Our 520 now has 4gb of memory.
    >
    > WIll we see performance issues arise as we implement the new SQL driven
    > access methods and procedures? Any gotchas we need to be aware of so we
    > don't unleash an embedded SQL that locks up the system?
    >
    > Would it be advisable up front to increase our RAM to say 16gb?
    >
    > A brave new world for us old record level access fogies.....



  3. Re: memory needs of SQL based system


    "Oldest Fossil Alive" wrote in message
    news:1167854108.142478.53040@48g2000cwx.googlegrou ps.com...
    > We are in the process of rewriting a major portion of our code to
    > switch from record level RPG access methods to using SQL procedures
    > and
    > embedded SQL.
    >
    > Our 520 now has 4gb of memory.
    >
    > WIll we see performance issues arise as we implement the new SQL
    > driven
    > access methods and procedures? Any gotchas we need to be aware of so
    > we
    > don't unleash an embedded SQL that locks up the system?
    >
    > Would it be advisable up front to increase our RAM to say 16gb?
    >
    > A brave new world for us old record level access fogies.....
    >

    It sounds like you bought into the lie IBM is selling. You will regret
    it.



  4. Re: memory needs of SQL based system

    Hi,

    first of all you should have a look at the database monitor, its the fastest
    way to analyze whats going on.
    the monitor will show up some index recommendations, you should follow this
    and create sql indexes.
    group PTFs might be an issue.
    if you transform your database access directly from rla to sql, it will take
    more hardware (might be 20 %) and it will get a little bit slower (maybe
    20%).
    the closer you go to the idea of sql (don't navigate through the data, just
    say what data you need), you will have benefit in hardware and speed and
    you will get more flexibility in your programs - first of all programmer
    productivity will be higher.

    Dieter Bender


    Oldest Fossil Alive wrote:

    > We are in the process of rewriting a major portion of our code to
    > switch from record level RPG access methods to using SQL procedures and
    > embedded SQL.
    >
    > Our 520 now has 4gb of memory.
    >
    > WIll we see performance issues arise as we implement the new SQL driven
    > access methods and procedures? Any gotchas we need to be aware of so we
    > don't unleash an embedded SQL that locks up the system?
    >
    > Would it be advisable up front to increase our RAM to say 16gb?
    >
    > A brave new world for us old record level access fogies.....



  5. Re: memory needs of SQL based system

    Are you suggesting 8G when the majority of the SQL is returning
    multiple rows or merging data from multiple files? That makes sense to
    me, but I'm not sure increasing the memory will make any / much
    difference if most of the SQL is returning a single row based on a
    (properly indexed) unique primary key.


  6. Re: memory needs of SQL based system

    An RPG single record read might be faster than an SQL single row query,
    but SQL comes into its own when the queries get complicated or the same
    operation has to be performed on multiple rows (e.g. when archiving).
    And hardware and SQL programmers are relatively cheap (and common)
    compared with good RPG programmers.

    Fast enough is fast enough, and a lot of the time SQL is fast enough -
    if you really wanted / needed great speed with the slowest / cheapest
    hardware, you'd write the apps in MI but who does that?


  7. Re: memory needs of SQL based system

    Thanks for the responses, all.

    We have a new boss who is a SQL Server guy, doesn't know the i-Series
    from beans, but upper management thinks SQL=modern, RPG="bad" legacy
    crap. All he knows is SQL, no RPG or COBOL. Instead of learning to
    program in native i-Series languages he wants to rewrite everything
    showing us old farts how to call SQL procedures he writes.

    Of course, we won't know, at least initially, the first thing about
    writing complicated SQL procedures, so debugging and fixing errors in
    this new environment will be a nightmare, but hey, that's progress,
    right?
    Elvis wrote:
    > When running in SQL intensive environment, rule of thumb is 8GB of main
    > memory per processor.
    >
    > You'll want to have good indexing strategy in place, in form of keyed
    > LFs and preferrably SQL indexes. Make sure you're current on DB group
    > PTFs.
    > If you can, take a query optimization class with IBM.
    >
    > Don't expect same performance for record-at-a-time type access. SQL is
    > designed for set-at-a-time access.
    >
    > If I was in your situation, I'd start to roll things out slowly, but
    > sounds like you guys are just going to bite the bullet.
    >
    > Good luck.
    >
    > Oldest Fossil Alive wrote:
    > > We are in the process of rewriting a major portion of our code to
    > > switch from record level RPG access methods to using SQL procedures and
    > > embedded SQL.
    > >
    > > Our 520 now has 4gb of memory.
    > >
    > > WIll we see performance issues arise as we implement the new SQL driven
    > > access methods and procedures? Any gotchas we need to be aware of so we
    > > don't unleash an embedded SQL that locks up the system?
    > >
    > > Would it be advisable up front to increase our RAM to say 16gb?
    > >
    > > A brave new world for us old record level access fogies.....



  8. Re: memory needs of SQL based system

    Hi,

    I don't know your boss, but I have written applications since RPG II up to
    ILE with RLA and with SQL. One SQL Statement often might be compared to
    several hundred lines of rpg code and testing a complex sql statement is
    often much more easy, just try it interactiv and you will see what you get.
    Debugging sql procedures isn't so hard to do (for the newer releases) and
    the sql code and the diagnostics generated by sql runtime are in most cases
    more clear than error handling in most RLA programs.

    Dieter Bender


    Oldest Fossil Alive wrote:

    > Thanks for the responses, all.
    >
    > We have a new boss who is a SQL Server guy, doesn't know the i-Series
    > from beans, but upper management thinks SQL=modern, RPG="bad" legacy
    > crap. All he knows is SQL, no RPG or COBOL. Instead of learning to
    > program in native i-Series languages he wants to rewrite everything
    > showing us old farts how to call SQL procedures he writes.
    >
    > Of course, we won't know, at least initially, the first thing about
    > writing complicated SQL procedures, so debugging and fixing errors in
    > this new environment will be a nightmare, but hey, that's progress,
    > right?
    > Elvis wrote:
    >> When running in SQL intensive environment, rule of thumb is 8GB of main
    >> memory per processor.
    >>
    >> You'll want to have good indexing strategy in place, in form of keyed
    >> LFs and preferrably SQL indexes. Make sure you're current on DB group
    >> PTFs.
    >> If you can, take a query optimization class with IBM.
    >>
    >> Don't expect same performance for record-at-a-time type access. SQL is
    >> designed for set-at-a-time access.
    >>
    >> If I was in your situation, I'd start to roll things out slowly, but
    >> sounds like you guys are just going to bite the bullet.
    >>
    >> Good luck.
    >>
    >> Oldest Fossil Alive wrote:
    >> > We are in the process of rewriting a major portion of our code to
    >> > switch from record level RPG access methods to using SQL procedures and
    >> > embedded SQL.
    >> >
    >> > Our 520 now has 4gb of memory.
    >> >
    >> > WIll we see performance issues arise as we implement the new SQL driven
    >> > access methods and procedures? Any gotchas we need to be aware of so we
    >> > don't unleash an embedded SQL that locks up the system?
    >> >
    >> > Would it be advisable up front to increase our RAM to say 16gb?
    >> >
    >> > A brave new world for us old record level access fogies.....



  9. Re: memory needs of SQL based system

    This sort of thing sometimes happens when someone has depth of
    experience, but not breadth of experience. It shouldn't be a question
    of what is "the best programming language", but what is the best
    programming language to solve the particular problem. If you are doing
    some building work, sometimes you want a hammer, sometimes a drill,
    sometimes a screwdriver, etc. Choosing one tool as "the best" and using
    it in every situation is madness.

    Is the new boss prepared to pit his SQL solutions against your "legacy"
    solutions in performance tests. Upper management might think
    SQL=modern, but they also know that new hardware (if it performs badly
    - sometimes SQL will outperform RLA, sometimes it won't) = $$$. And how
    does he intend to get SQL to do print files, display files, message
    queues, dataqueues etc.?

    Hopefully you can persuade the new guy that whilst SQL is the right
    tool for the job in some cases, in other cases it will be RPG, or
    COBOL, or CL, or Java, or C, or PHP etc.


+ Reply to Thread