Question & Answer
Question
How do I add zip codes that are not provided in a default IBM Sterling Order Management installation?
Cause
IBM Sterling Order Management provides a list of zip codes as part of the default installation, but if you find that some zip codes are not present, use the following process to add additional zip codes.
Answer
1. Take backup of the US_ZipcodeLocation.sql file present under "<DEVTOOLKIT_RUNTIME>/database/FactorySetup/Optional/db2/ZipCodeLocation" location to a <temp> directory.
2. Create a new file with the same name US_ZipcodeLocation.sql, this is used to insert the custom entries
3. Add custom entries to the newly created blank US_ZipcodeLocation.sql, for example:
2. Create a new file with the same name US_ZipcodeLocation.sql, this is used to insert the custom entries
3. Add custom entries to the newly created blank US_ZipcodeLocation.sql, for example:
insert into YFS_ZIP_CODE_LOCATION (ZIP_CODE_LOCATION_KEY, ZIP_CODE, STATE, CITY, LONGITUDE,LATITUDE,COUNTRY, CREATEUSERID, MODIFYUSERID, CREATEPROGID, MODIFYPROGID, LOCKID) VALUES ('20230203144010_Extn','85288','AZ','Tempe',-111.9561,33.4487,'US','SYSTEM','SYSTEM','SYSTEM','SYSTEM',0) ;
commit;
extensions.jar > extensions\files\extensions\global\database\FactorySetup\Optional\db2\US_ZipcodeLocation.sql
5. [Legacy only] Manually copy the extensions.jar to your dropbox server, from where the IBM Self Service task picks it up for processing.
6. Initiate a Build and Deploy
7. Run the "Load Optional DB2 Scripts" IBM Self Service process [Custom DB2 scripts]
Notes:
1. If a failure message is observed for records already present (both for region schema and Zip Code location) those can be ignored.
2. Make sure that the ZIP_CODE_LOCATION_KEY is unique for any custom entries. It should also not clash with any new records released in future Order Management updates.
6. Initiate a Build and Deploy
7. Run the "Load Optional DB2 Scripts" IBM Self Service process [Custom DB2 scripts]
Notes:
1. If a failure message is observed for records already present (both for region schema and Zip Code location) those can be ignored.
2. Make sure that the ZIP_CODE_LOCATION_KEY is unique for any custom entries. It should also not clash with any new records released in future Order Management updates.
Suggestion for key : <Year><Month><Date><Time>_Extn = 201807171511_Extn
3. Avoid appending to the default US_ZipcodeLocation.sql file as duplication errors may cause the "Load Optional DB2 Scripts" process to not pick up your custom zip code location entries.
4. Take care to ensure the .sql file is placed in the correct path
5. When loading new zip codes for the first time, test in a lower environment first (such as QA) and perform thorough testing.
6. When loading a larger dataset, the following strategy can be used to achieve better performance with the load process:
Batch multiple record inserts into a single insert statement (around ~100 per statement)
Batch multiple record inserts into a single insert statement (around ~100 per statement)
insert into YFS_ZIP_CODE_LOCATION values (
(Record1),
(Record2),
(Record3),
...
...
);
If INSERT statements are not properly batched, the data load process may time out and not complete successfully.
Refer to the following IBM Documentation for additional information:
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS6PEW","label":"Sterling Order Management"},"Component":"","Platform":[{"code":"PF016","label":"Linux"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Was this topic helpful?
Document Information
Modified date:
22 February 2024
UID
ibm10795416