001 How to enable MongoDB connections

Enable MongoDB connection objects in the ETL repository with the required upgrade procedure and repository SQL updates.

Repository Database Updates
4.9 ★★★★★ Based on 16 reviews on Capterra See all reviews on Capterra →

The Update procedure

This update adds MongoDB connection objects to the repository and applies the supporting timeout field changes required by newer product versions. Use it when the software supports MongoDB connections but the repository still has the older structure. MongoDB may be document-based, but the repository still wants its rows in order.

For VI version 9 and AETL version 6 upgrades

Follow this path when upgrading from Visual Importer ETL version 9 or Advanced ETL Processor version 6. Create the backup first, then update the repository before starting the new installation.

  1. Create repository backup.
  2. Stop all services (Enterprise version only).
  3. Uninstall software.
  4. Upgrade repository.
  5. Install software.
Do not skip the backup. Repository upgrades are quick when they work and memorable when they do not. A backup is the difference between recovery and a very educational afternoon.

Run the MongoDB object update first

This SQL adds the MongoDB connection object types, creates the MongoDB node under the connection tree, and assigns the object type group used by the designer.

All repository types

Insert into OBJECT_TYPES
(OBJECT_TYPE,OBJECT_TYPE_NAME)
Values
('903','MongoDB Connection')

Insert into OBJECT_TYPES
(OBJECT_TYPE,OBJECT_TYPE_NAME)
Values
('904','MongoDB Connections')

INSERT INTO OBJECTS_TREE
(OBJECT_ID,
PARENT_ID,
NAME,
OBJECT_TYPE
)
Select
OBJECT_ID*-4 as OBJECT_ID,
PARENT_ID,
'MongoDB' as NAME,
903 as OBJECT_TYPE
From
OBJECTS_TREE
where OBJECTS_TREE.OBJECT_TYPE=320

UPDATE OBJECT_TYPES
set OBJECT_TYPE_GROUP=22000
WHERE OBJECT_TYPE= '903'

Run the timeout field update for your repository type

The next SQL block adds `TERMINATE_ON_TIMEOUT` to the repository tables used by queues, queue history, schedules, and event monitors. Run only the section that matches your repository database engine.

For Oracle

ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL
ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL
ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL
ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL

For MS SQL Server

ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0
ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0
ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0
ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0

For MySQL

ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0

For PostgreSQL

ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0

For Interbase

ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMERIC (1) default 0,
ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0

For VI version 8 and AETL version 5 upgrades

Older repositories need a restore into a newly created repository before the final MongoDB object tree update. This avoids carrying forward repository structure that the newer version no longer expects.

  1. Create repository backup (backup must have one file inside).
  2. Stop all services.
  3. Uninstall software.
  4. Install software.
  5. Create a brand new repository and connect to it.
  6. Restore repository from backup.
  7. Run the following SQL against the repository:
INSERT INTO OBJECTS_TREE
(OBJECT_ID,
PARENT_ID,
NAME,
OBJECT_TYPE
)
Select
OBJECT_ID*-4 as OBJECT_ID,
PARENT_ID,
'MongoDB' as NAME,
903 as OBJECT_TYPE
From
OBJECTS_TREE
where OBJECTS_TREE.OBJECT_TYPE=320

UPDATE OBJECT_TYPES
set OBJECT_TYPE_GROUP=22000
WHERE OBJECT_TYPE= '903'

After the repository update

Restart the product and check the connection tree. MongoDB should now appear as an available connection option. If it does not, confirm that the application is connected to the repository database you updated.

Note: If you are having problems upgrading our software please let us know and we will do our best to assist you.

By DB Software Laboratory Last updated