Prev
Next
Forums S3 Systems Administration

Custom SQL table index not working

Sort:
You are not authorized to post a reply.
Author
Messages
Joe O'Toole
Veteran Member
Posts: 314
Veteran Member
11/18/2010 9:45 PM
    I need to create a custom index on the Paymastr table which will basically be the same as Pymset4 except with check date descending. I decided to change the Lawson delivered Pymset4 index in our Test area to confirm I would get the desired results with the new index before I write code to leverage it. I changed the index attributes in SQL server and also in Dbdef to reflect the descending index element in Pymset4 and a raw SQL query using that index returns the data in the new order as expected. Lawson apps however are still returing the records in ascending date order. I cleared all IOS caches but have not been able to bounce the Lawson or SQL services yet. Any suggestions on what I am missing?
    Ragu Raghavan
    Veteran Member
    Posts: 469
    Veteran Member
    11/18/2010 10:10 PM
      after the changes in dbdef, I think you need to run
      1. blddbdict
      2. dbreorg
      John Henley
      Senior Member
      Posts: 3348
      Senior Member
      11/19/2010 12:01 AM
        You also should recompile =
        Thanks for using the LawsonGuru.com forums!
        John
        Sam Simpson
        Veteran Member
        Posts: 239
        Veteran Member
        11/19/2010 2:50 PM
          Joe,
          Modifying a Lawson delivered index is a bad idea. You will end up recompiling all programs that uses it. Besides Lawson will not support you. The best way is clone the index and include your modification then do blddbdict and dbreorg as previously mentioned.
          Joe O'Toole
          Veteran Member
          Posts: 314
          Veteran Member
          11/19/2010 3:01 PM
            I agree, I'm just doing this in our test area for proof of concept.
            I've already cloned the index with the new setting I will use in the customized application.
            Thanks for everyone's feedback!
            Joe O'Toole
            Veteran Member
            Posts: 314
            Veteran Member
            11/19/2010 8:09 PM
              John - I ran blddbdict, dbreorg and recompiled HR and PR systemcodes. New index PYMSETJ is visible in dbdef and externally from Lawson via SQL. IOS / EMSS drill returns error "index PYMSETJ is not valid for PAYMASTR". Any other thoughts? Here is the IOS call:

              "http://server.domain.com/servlet/Router/Drill/Erp?_TYP=OS
              &_PDL=TEST8&_SYS=HR&_FN=PAYMASTR
              &_IN=PYMSETJ&K1=1111&K2=22222&K3=20090910&K4=810750&K5=1885852
              &_OUT=XML&_XSL1=http://server.domain.com/lawson/webappjs/drillTOhtml.xsl
              &_XSLPARAM=back|true              "

              I'm also seeing reference to _RID=HR-EMP-V-0004 in the previous IOS call:

              "http://server.domain.com/servlet/Router/Drill/Erp?_TYP=OV&_PDL=TEST8&_SYS=HR
              &_RID=HR-EMP-V-0004
              &_IN=EMPSET1&K1=1111&K2=22222&_OUT=XML
              &_XSL1=http://server.domain.com/lawson/webappjs/drillTOhtml.xsl
              &_XSLPARAM=back|true"

              It appears to be some predefined view or relation that is referenced withing the EMSS JS code.
              Anyone know where the definition is located?
              Joe O'Toole
              Veteran Member
              Posts: 314
              Veteran Member
              11/19/2010 9:55 PM
                Ok so I found the rule HR-EMP-V-0004 in the HR.or file in HRSRC but it makes no reference to paymastr table where we are adding the custom index:

                DEFINE OBJVIEW "Payments"
                ID HR-EMP-V-0004
                FILENAME EMPLOYEE
                VALIDITY PAYMENTS-EXIST
                RELATION PAYMENTS
                CONDITION NOT-ADJUSTMENT
                DSPFLDS CHECK-NBR: "Payment Nbr", CHECK-DATE: " Date", GROSS-PAY:"Gross Amount ",
                NET-PAY-AMT: "Net Amount ", PRCHECK.TYPE-1: "Type", STATUS: "Status"
                Joe O'Toole
                Veteran Member
                Posts: 314
                Veteran Member
                12/22/2010 12:58 PM
                  Ok for anyone interested heres a brief summary on how I set this up and got it working:

                  - Define new index on SQL table (you can verify it works properly by querying with transact SQL referencing the new index)
                  - Edit the "OR" file to create a new rule referencing a new relation (see existing OR entries as an example)
                  - In Dbdef define the new relation for the table based on the new index
                  - Run a Blddbdict (new index will be detected and added if the previous steps worked properly)
                  - Run a Srgen if Blddbdict does not do one
                  - Run Dbreorg
                  - Recompile affected system codes (do all if you are not sure what is affected otherwise you may get database timestamp errors in some applications that use the modified table)

                  This worked well in our case as the new drill rule automatically showed in HR11 and EMSS so no modifications to the production Lawson code was needed. You could of course modify Lawson code or your custom programs to use the new relation as well.
                  You are not authorized to post a reply.