How do you populate or update dates stored as numeric values?


I added a new date field, cstReminderDateDA, to a custom business object. For existing records, there is a requirement that cstReminderDate be set to the value of an existing date field (cstDueDateDA) minus 30 days, i.e. cstReminderDateDA = (cstDueDateDA – 30). This would be pretty straightforward, except that both cstReminderDateDA and cstDueDateDA are stored as numeric fields in our Microsoft SQL Server database. How do I populate cstReminderDateDA?

IBM TRIRIGA stores the date as epoch time. See the following wiki link explaining this. If you do some searching, you will find some functions available for SQL Server for data calculations.

[Admin: To see other related posts, use the Epoch Time tag.]

Continue reading

Advertisements

How do you prevent SQL database log from crashing the database?


I made a big import of data in TRIRIGA, but the resources were not enough to proceed. So I have to stop TRIRIGA and truncate all events to stop the import. But now, the database log never stops growing and crashing the database. Is there a way to clean up and make TRIRIGA stable?

If this is Microsoft SQL Server, this may be related to the following SQL Server defect: SQL Server crashes when the log file of tempdb database is full in SQL Server 2012 or SQL Server 2014.

[Admin: To see other related posts, use the SQL tag or “sql server” search phrase.]

Continue reading

Is there any tool which imports currency conversion rates?


We are looking for an integration solution which allows you to import currency conversion rates. It seems that this data is not based on a BO, so I think traditional integration tools such as DataConnect (DC), Data Integrator (DI), and Integration Object won’t work. Have you seen this kind of requirement? Are there any solutions other than using SQL script? I’ve tried the SQL below, and it seems to be working.

INSERT INTO BUDGET_CURRENCY_CONVERSION (CONVERSION_GROUP, FROM_CURRENCY_CODE, TO_CURRENCY_CODE, CONVERSION_RATE, START_DATE, END_DATE, INSTANCE_ID) VALUES (‘LIBA’, ‘Chinese Yuan’, ‘Euro’, 0.13, {ts ‘2017-01-01 01:00:00’}, {ts ‘2017-12-31 01:00:00’}, BUDGET_CURRENCY_ID_SEQ.nextval)

BusinessConnect (a.k.a. Connector for Business Applications or CBA) is the best method to use for this. Here’s the PDF. The putCurrencyConversionRates method is what you would want to use.

[Admin: To see other related posts, use the Currency tag.]

Continue reading

Can you get the Cleanup Agent to remove record data in chunks?


In one of our environments, we have a large amount of records that have been transitioned to the null state. When the Cleanup Agent runs, it runs out of DB2 transaction log space executing the following:

DELETE FROM IBS_SPEC_ASSIGNMENTS WHERE EXISTS (SELECT ‘X’ FROM IBS_SPEC_CA_DELETE WHERE IBS_SPEC_CA_DELETE.SPEC_ID=IBS_SPEC_ASSIGNMENTS.SPEC_ID)

For workflow instance saves, the Cleanup Agent now seems to remove the data in small chunks (of 1000 rows each). But for the record data cleanup, it still seems to (try to) remove all data in one huge SQL statement/transaction. Is it possible to get the Cleanup Agent to remove record data in chunks, like it does for workflow instance saves?

Otherwise, I’m thinking of writing a small util that would run the statement above, but in smaller chunks, since it seems we still have the list of record IDs that it tries to remove in the IBS_SPEC_CA_DELETE table. Any obvious issues with that?

As of 3.5.3, there have been no changes for the data to be deleted in chunks for IBS_SPEC_ASSIGNMENTS. This sounds like a good PMR. It is rarely recommended to delete records directly from the database, but in this circumstance, you might be okay. However, the IBS_SPEC_CA_DELETE only exists during the time the Cleanup Agent is running. It is dropped when the agent goes back to sleep.

[Admin: To see other related posts, use the Cleanup tag or SQL tag. As a side note, starting with version 3.4, the Cleanup Agent name was changed to Platform Maintenance Scheduler.]

Continue reading

How do you get high-availability with a database-as-a-service?


The most critical aspect of all production systems is being able to ensure high-availability in case of critical failures. The traditional approach was to operate a disaster recovery site and fail-over clusters. This requires 24×7 maintenance and monitoring activities and adds the significant costs for hardware, software, and human resources engaged. Due to this, many companies opt-out of having the high-availability for their EAM system.

Thankfully, there is a new option with database-as-a-service (DBaaS) offerings being provided by all major database vendors. Providing a high-availability system for 10,000 users can now be achieved by filling the single web form. You will then be provided with a database service that is available 24×7 and all maintenance, monitoring, and backup activities are automated in the background.

We tested DBaaS offerings from several vendors such as Amazon, Microsoft, and Oracle and found all of them being able to deliver on their promise. Although some products like IBM Maximo and IBM TRIRIGA are not officially certified for these platforms, we managed to successfully migrate several clients to a DBaaS database on Oracle and MS SQL Server…

[Admin: To see other related posts, use the High Availability tag or SaaS tag.]

Continue reading

Can anyone help with extracting data via Oracle SQL Developer?


Does anyone have any experience in extracting TRIRIGA data from the database directly through Oracle SQL Developer? I am trying to extract through SQL code, and combine in one table, the data from triSpace, triSpaceClassCurrent, and triSpaceStandardsSpec. The idea is to create a single table with all spaces, space classifications, and space standards.

[Admin: To see other related posts, use the Oracle tag or SQL tag.]

Continue reading

How do you delete records that exist in T_ table not IBS_SPEC table?


We have some corrupted records, which appeared across 2 different BOs so far. We can’t delete them from the application level, and the cleanup script won’t delete them either, because they can’t be found under the IBS_SPEC table. Does anyone have a SQL to properly cleanup those records from the T_ table?

[Admin: This post is related to the 02.10.15 post about purging records from a BO. To see other related posts, use the Cleanup tag or SQL tag.]

Continue reading