• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Infor / Lawson Platforms S3 Systems Administration

Custom SQL table index not working

 7 Replies
 0 Subscribed to this topic
 27 Subscribed to this forum
Sort:
Author
Messages
Joe O'Toole
Veteran Member Send Private Message
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 Send Private Message
Posts: 477
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
Send Private Message
Posts: 3351
11/19/2010 12:01 AM
You also should recompile =
Thanks for using the LawsonGuru.com forums!
John
Sam Simpson
Veteran Member Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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.