Tag: database

A database is an organised collection of data stored and accessed electronically.

Posted on

July Newsletter 2023

Welcome to the July 2023 newsletter from 3SL!

This newsletter contains a mixture of news and technical information about us, and our requirements management and systems engineering tool “Cradle”. We would especially like to welcome everyone who has purchased Cradle in the past month and those who are currently evaluating Cradle for their projects and processes.

We hope that 3SL and Cradle can deliver real and measurable benefits that help you to improve the information flow within, the quality and timeliness of, and the traceability, compliance and governance for, all of your current and future projects.

If you have any questions about your use of Cradle, please do not hesitate to contact 3SL Support.

PDUIDs

When we work with information, we need a way to distinguish each piece of information from all other pieces of information so we can be sure we have found what we were searching for. We do this by marking each piece of information in a unique way.

For information in databases, the markings are unique values called keys or identities. A piece of information can have multiple identities, each for a different purpose. For example, although a company’s payroll system is likely to identify each person by a unique Employee ID, each person’s details will also include their governmental tax ID (such as a National Insurance number, a Unique Taxpayer Reference, a Sozialversicherungsnummer or a Social Security Number). This tax ID will also be unique and so could also be used as an identity for that person’s information.

Cradle has two forms of identity, item identities and Project Database Unique IDs (PDUIDs).

We will publish a series of blog posts about PDUIDs, describing what they are, how to view them, how to use them, and how PDUIDs can be managed when you import information into your databases.

Item Identities

There are several basic item types in Cradle. Each basic item type uses a different combination of attributes to create a unique Item ID for items of that type:

Item identities table

An item is identified by this Item ID and a unique Instance ID, typically a version and draft.

PDUID Structure

Project Database Unique IDs (PDUIDs) are a single, consistent, numbering system for all database information. Each PDUID is a 26 character string that contains a Database ID to identify a Cradle system, a Project ID to identify a project database and a Unique ID:

Cradle PDUID Structure
Structure of PDUIDs

A PDUID references all instances of an item. Therefore the combination of a PDUID and an Instance ID (a version and draft) will identify a specific item. So, this is an alternative to the Item ID and Instance ID and has the advantage of being consistent and a single numbering sequence for all types of item.

Further Details

For further details in this part 1 of a description of PDUIDs, please see the full blog entry here.

Remote Databases

A Cradle system can contain any number of databases. For the best performance, we recommend that databases are stored on disks connected to the machine that runs your Cradle Database Server (CDS). But, this may not be possible.

For example:

  • The local system may not have enough disk space available
  • The information in the database may be classified and must be stored separately

Here each database will be stored on a remote filesystem that must be referenced by a pathname so the CDS can work with it.

Further Details

For further details of remote databases, please see the full blog entry here.

Over Half Way Through the Year

It’s true; the 2nd July marked the halfway point of the calendar year. That went fast didn’t it?

It feels like we only just celebrated the New Year and now we are six months away from doing it all over again.

At this time of year, it is good to reflect on what’s already passed and what is to come this year. Here are some ways that might help if you are looking to refocus and recharge over the summer months.

Check in on Team Goals

  • How are the goals the team set at the beginning of the year going?
  • When was the last time your team reviewed them?

Now is a great time to reflect on any progress. Is your team on track? Is everybody on the same page?

Whatever the progress so far this year, there will be lessons to be learnt from it. It’s time to put an action plan in place for the remainder of the year. Now is a good time to get the team goals back on track:

  • Ask who do you need to help achieve those goals?
  • What’s the best way to communicate with them?
  • Is there an alternative way to achieve them?

Communication

Summer can be especially busy; school summer holidays, weekly events, fewer people in the office, and various demands can bring stress to everyone.

With all these additional activities going on, it’s easy for people to get distracted, lost and even burnt out.

Now is a great time to contact your team, employees and other connections. It can be as simple as a chat over a cup of coffee, a walk and talk or a business/working lunch. This will allow you to connect in a more casual way, which in turn, can help strengthen the link between you and your team.

Help your Team Avoid a Summer Decline

It’s no surprise that productivity can fall off a cliff when the sun comes out! Thoughts of ice cream, beer gardens and future holidays can lead our minds to wander off and our focus can end up in the bin.

Ice cream
Ice cream

Now is a good time to prepare your team and business to avoid any slump.

Congratulate your team on their efforts so far this year. One way to keep the momentum going is to set small achievable goals, something that can be done within a week to a month can help. As you complete and reach each one, the team will get a boost.

Having weekly/monthly meetings can allow the team to see those goals that have been achieved. Using metrics, dashboards and graphs can help your team see the progress made each week, month, year or more.

This progress will give reasons to celebrate and that can only be a good thing!

Remember: the team working together will make the dream work!

Feedback

We continue to receive positive feedback from our customers. We really appreciate ALL feedback, as this helps us to assess and improve both the products and services we provide.

In June, we provided a Cradle training course to one of our customers in Australia. They kindly sent the following feedback:

“Extremely informative classes. We are very appreciative of the customised content tailored for our envisaged use of the tool”

Independence Day (4th July)

4th July was a federal holiday in the United States commemorating the Declaration of Independence which was ratified by the Second Continental Congress on July 4th 1776, establishing the United States of America.

Social Media

We commemorated #DDay – 79 years ago. “We will remember them“:

DDay
DDay

Some of our customers, both old and new, attended various shows/exhibitions etc, e.g.:

@SercoGroup announced they have been awarded nine contracts to help the #IRIDE space programme. This programme is led by the Italian government and implemented by the European Space Agency. This is one of the most amibitous Earth Observation programmes in Europe.

With electric vehicles taking over the roads, our customer @Enphase talked about EV chargers.

Looking Back

Last month we discussed:

We would also like to thank all attendees on our Configuration Management course which we provided in June.

Still to Come this Month

Your Highlights!

If you have any company news or achievements that you would like 3SL to share in any of our newsletters then please let us know.

Posted on

Remote Databases

A Cradle system can contain any number of databases. For the best performance, we recommend that databases are stored on disks connected to the machine that runs your Cradle Database Server (CDS). But, this may not be possible.

For example:

  • The local system may not have enough disk space available
  • The information in the database may be classified and must be stored separately

Here each database will be stored on a remote filesystem that must be referenced by a pathname so the CDS can work with it.

Remote Linux Filesystems

On Linux, a remote filesystem is mounted as a pathname. The CDS works through this pathname to access the remote filesystem. Remote filesystems include NFS (network file system) and others.

3SL does not recommend the use of CIFS (Common Internet File System) filesystems. We have seen several Linux systems that use CIFS to mount remote Windows filesystems. Sadly all these CIFS filesystems have provided very poor performance to the Linux server.

For NFS mounts, we suggest that you examine block sizes of 4K or more and consider nosuid mounts.

Remote Windows Filesystems

We are sure you are familiar with drive letters such as G: or H: that are the connection point to filesystems on remote systems.

For example, a filesystem disk1 on server myfileserver1 can be mounted as the X: drive. You can navigate to a pathname on X: such as:

X:\Cradle Databases\ABCD

You cannot use drive letters in the pathname to create a Cradle database on a remote drive. Drive letters are specific to a Windows profile and are created only when that profile is active.

So, please use UNC pathnames to refer to Cradle databases on remote drives. UNC pathnames are of the form:

\\servername\path

So in the above example, the pathname to the ABCD database would be:

\\myfileserver1\disk1\Cradle Databases\ABCD

Access to Remote Windows Databases

You must ensure that the access rights to remote directories will allow the CDS to search all directories inside the database and give RW access to all of the database files. Please note that the CDS runs as the local SYSTEM user on the machine where it is run. So it is this user who must have search, read and write access on the remote filesystem.

If you wish, you can control the user who will run the CDS. To do this iteratively (you may need to have Administrator rights depending on your system):

  1. Open Task Manager and in the Services tab, stop the Cradle Services Manager
  2. Open Windows Explorer
  3. Navigate into your Cradle system and look in the folder bin\exe\windows
  4. Press SHIFT and right-click on the CDS executable: crsvr.exe and choose Run As and enter the username and password that you want the CDS to run as

If you want to permanently change how the CDS runs, then (you may need to have Administrator rights on your system):

  1. Open services.msc, the MMC (Microsoft Management Console) or Services Control Manager (depending on your version of Windows) to see all services and stop the Cradle Services Manager
  2. Select the Cradle Services Manager, right click and choose Properties
  3. Select Log On in the dialog and specify the user that you want the CDS to run as and the password for this account:Run As User for 3SL Cradle Database Server
  4. Click OK to close the dialog
  5. Start the service and then close the list of services
Posted on

Checking Database Integrity

Data integrity refers to the accuracy, completeness and reliability of data stored within your Cradle database. Checking database integrity is important as it guarantees both the traceability and searchability of your data. It also increases the performance and stability of your data. Maintaining the integrity of your data is essential. As you collect more and more data within  your Cradle project, it is a priority to maintain the integrity of this data, otherwise the data is worthless.

Error checking ensures that your Cradle data hasn’t been compromised, e.g. during upgrades/imports etc. We recommend that integrity checks are done on a regular basis.

In Cradle, there are a couple of ways to check the integrity of your database:

Please note that these checks must only be run when there are NO other users active in the database. If you are in doubt as to whether you are the only user active in the database, then DO NOT run these checks. If you do, damage can occur.

Item Integrity

The Item Integrity option allows you to perform integrity checks on your Cradle database. This option is available from the Project tab in WorkBench:

Screenshot of the Item Integrity dialog
Item Integrity dialog

The Frame Version Checks detect missing versions of any frames or missing records in any version of any frame for the item type specified. Errors  can be fixed using the Fix button.

There are many Item Checks which are explained here.

Many of these checks can be made subject to one of the following scopes:

  • Superseded or retired – Selects items with a status of S (Superseded) or T (Retired)
  • Latest baseline – Selects items with a status of B (Open Baseline)
  • Deleted – Selects items with a status of D (Deleted)
  • Current –  All current items

If you want to produce a report of the item integrity check, ensure to select the Generate a report checkbox.

Cross Reference Integrity

The Cross Reference Integrity Check option checks the cross references in your Cradle database.

Screenshot of the Cross Reference Integrity dialog
Cross Reference Integrity dialog

The current set of cross references is scanned to find any cross references that are invalid. Any cross reference is only counted once in these checks. So if a cross reference is dangling and also invalid, it will only appear once in the counts.

The Cross Reference Checks are explained here.

When the integrity check has been ran you have the following options:

  • Fix – Fix the cross references
  • Export – Export the group to a CSV file
  • List – List the group individually
  • Delete – Delete the cross references from the group

The Fix and Delete options for privileged users must only be run when there are no other active users in the database. If you are in any doubt, do NOT run these operations.

If you want to produce a report of the cross reference integrity check, ensure to select the Generate error report checkbox.

Posted on

Open Database Connectivity (ODBC)

Open Database Connectivity (ODBC) is an application that allows different types of databases to interface through the use of a library containing data access routines. Cradle now supports connections to DISAM, Oracle and MySQL.

Connection to Different Data Storage Types
This diagram shows the logical representation of the project data storage

Installation Requirements

Before installing check the minimum hardware and operating system requirements for ODBC. The following are necessary for a successful installation:

  • An Oracle or MySQL installation accessible to the CDS preferably on the same machine
  • An ODBC driver manager
  • An ODBC driver for the data source you wish to access. For example, Oracle in Oracle for Windows or SQL Server

Please ensure the versions are the following or greater:

  • Oracle in Oracle from Oracle Corporation version is 18c
  • MySQL ODBC 8.0 Unicode Driver from Oracle Corporation is version 8.0.16
  • Windows ODBC Data Source Administrator appropriate for your Windows release
  • unixODBC driver Manager 2.3.7 or later

*Cradle install will not work with Oracle databases that have been setup using the ‘Create as Container Database’ option.

ODBC is a licence option which can allow Oracle or SQL or both to work with our Cradle Enterprise version. This new licence is not available for any other version of Cradle. For enquires about the new ODBC licence, please email salesdetails@threesl.com.

Cradle and ODBC

The users can still import and export standard export, CSV, XML and now ReqIF files. If a user is on Windows they can still use our Toolsuite applications. The only difference is the access to databases either directly (DISAM) or through ODBC (Oracle and MySQL).

With Cradle supporting ODBC, a user can now install Cradle on the same database server farm as the Oracle/MySQL databases. A separate server is no longer required.  Although projects can be on a separate server and linked to the Cradle database using a UNC path e.g. \\hostname\path\projects\mmh1. Click for more information on Storing Project Databases in NAS.

3 different projects on 2 different servers
Cradle About WorkBench UI showing 3 different projects on 2 different servers

Installing ODBC

The CDS requires an installation of ODBC on the same machine for either Linux or Windows. Client machines which don’t have the CDS on them don’t need to have ODBC installed at all.

Linux

If the Linux distribution you are installing on does not have v2.3.7 of unixODBC as a package, then you’ll need to build this from source. You can download and find installing steps for it from http://www.unixodbc.org/download.html. Ensure the user installing is the System Administrator or a user with administrator privileges for installing both ODBC and Cradle.

Check for the following files on your Linux installation:

libodbc, libodbccr and libodbcinst
Necessary files on Linux

Windows

If the Windows platform you are installing on does not have ODBC please install the latest Oracle in Oracle for Windows. You can find installing and upgrading documents for Oracle Database here and select the release you are on from the drop-down.

Certain settings are required to be selected when using Oracle in Oracle. Check and enable or disable the following options in the Oracle ODBC Driver Configuration checking all numbers:

Oracle ODBC Driver Configuration
Oracle ODBC Driver Configuration for Application, Oracle and Workarounds

Turn on the Connection Pooling attributes and use the default options:

Pool Connection settings
Turn on Connection Pooling Attributes

Setting up with Cradle

After installation of both Oracle and Cradle, the administrator is required to check and alter the following files correctly:

  • Support file for the driver when required on Windows usually in ‘Oracle <version>\network\admin’

The following example of a tnsnames.ora file, shows it can have more than one Oracle database location set within it.

tnsnames.ora file
tnsnames.ora file
  • Correctly configure the ODBC file for Cradle in %CRADLEHOME%\admin\db_config\odbc called odbc_config

All options are hashed out in the file until an administrator changes them. All options show an example of what can be entered. This is an odbc_config example for just Oracle on one of our installations.

CRADLE_VERSION = 7.7
#
ODBC_DRIVERINFO = ORACLE
    CONFIG_DIR = 'oracle'
    CHARSET_METHOD = ENVIRONMENT
    CHARSET_VAR = 'NLS_LANG'
    CHARSET_BDY = '.'
    CHARSET = 'AL32UTF8'
    CONNECT_STR = 'DSN=$SOURCE;UID="$USER";PWD="$PASSWORD";'
ODBC_DRIVERINFO = END
#
#
ODBC_DRIVER = ORACLE
    ODBC_SOURCE = 'OracleODBC-18c-ORAC'
    DBMS_NAME = 'Oracle'
    DEFAULT_DB_TABLESPACE = 'USERS'
    DEFAULT_DB_SCHEMA_USE = DEFAULT
    DEFAULT_DB_SCHEMA = 'cradle_prj_$PCODE'
    DEFAULT_USER = 'CRADLE_CDS_USER'
    DEFAULT_PASSWORD = '***'
ODBC_DRIVER = END
ODBC_DRIVER = ORACLE
    ODBC_SOURCE = 'OracleODBC-18c-ORAC12'
    DBMS_NAME = 'Oracle'
    DEFAULT_DB_TABLESPACE = 'USERS'
    DEFAULT_DB_SCHEMA_USE = DEFAULT
    DEFAULT_DB_SCHEMA = 'cradle_prj_$PCODE'
    DEFAULT_USER = 'CRADLE_CDS_USER'
    DEFAULT_PASSWORD = '***'
ODBC_DRIVER = END
  • Correctly configure the file create_CRADLE_CDS_USER in cradle\admin\db_config\odbc\setup_scripts

The default user tablespace and users within your Oracle database need to be set within this file. This is to allow the altering and creating privileges for the users. In our original it has:

  • USER “CRADLE_CDS_USER”
  • DEFAULT TABLESPACE “USERS”

The privilege options in create_CRADLE_CDS_USER should be changed to your own tablespace and user names used in the odbc_config file.

Creating New Projects

Users get the same Cradle interface as before but with a new section it now allows connects to 3 different types of database. The different databases can be created through Project Manager by selecting a different Data Source.

Showing 2 different Oracle databases in Create Project UI
Showing 2 different Oracle databases either can be used

Once a source is selected, a new section allows the default settings from the odbc_config file to be bypassed if required:

""

Projects can be still be created through a command line using c_prj using the new -odbc_src option.

[-odbc_src <odbc_source>
 [-odbc_sch {DEFAULT | DATABASE | NONE | <ODBC schema>}]
 [-odbc_usr <ODBC user>]
 [-odbc_pwd <ODBC password>]]
c_prj.exe -create -code TEST -title "Test Project" -path /home/project/odbc -pid ODBC01 -schema DEFAULT -odbc_source OracleODBC-18c-ORAC -odbc_user -odbc_pwd

Both -odbc_user and -odbc_pwd are left blank so the default user and password from the odbc_config file will be used. They will only be filled when an override is required. When creating a database for Oracle then the -odbc_sch DEFAULT would be used. For an SQL database then -odbc_sch DATABASE would be used.

File Locations

All items requirements, system notes and diagrams etc., will be located in an Oracle database but there are some files that are kept in a project folder like in a DISAM project.

The ‘prj_params’ file can be found in the project folder with a new file called ‘connection_config’. The ‘prj_params’ is the same as before, with all the options for the project schema and user interactions. The new ‘connection_config’ file holds the version, type, database source and odbc schema used. It also holds the User and Password to override the DEFAULT USER / PASSWORD from the odbc_config.

CRADLE_VERSION = 7.7
TYPE = PDB_CONN_ODBC
SOURCE = OracleODBC
USER = *****
PASSWORD = *****
DB_SCHEMA_USE = DEFAULT

The definitions e.g. views, queries and reports etc., are still held in the definitions folder under the different user types. Source and Formal Documents are also held as before in the doc and fdoc folders.

*****NOTE*****

Direct manipulation of data in Cradle’s data files held in DISAM or an ODBC supported database is not recommended under any circumstance. The inherent integrity of the data and its internal relationships can only be maintained by accessing through Cradle’s defined UI, command-line or API tools.

  • We do not provide any information about Cradle’s use of Oracle and
    MySQL other than which we provide in our documentation
  • We do not provide any information or assistance to anyone who is
    proposing to access Cradle’s data that is stored in Oracle or
    MySQL other than through Cradle
  • Anyone who accesses, either read-only or read-write, Cradle data
    that is stored in Oracle or MySQL without using Cradle as the only
    means to access that data, does so entirely at their own risk and
    3SL will not accept any responsibility for, nor provide any
    assistance to, anyone who accesses Cradle data in that way and then
    subsequently finds that their data is no longer accessible through,
    or manipulable by, Cradle
Update:

July 2020 – Directory updated

Posted on

Storing Project Databases in NAS

We are sometimes asked about storing project databases in NAS (network attached storage), is it possible, is it a good idea, and how to do it.

NAS

Network attached storage is a type of storage device that attaches directly to a network. It is typically a RAID array of disks with one or more interfaces and some management software. It provides network storage without a need to manage a server with attached storage.

A NAS storage device
A Typical NAS Device

Storing Project Databases in NAS

Each Cradle project database is a directory of files and subdirectories. It can be stored anywhere. So, yes, you can store a Cradle database on a NAS device.

Since the NAS device is not a server in the normal sense, it will not be running any part of Cradle and, in particular, will therefore not be running the Cradle Database Server (CDS).

Therefore storing Cradle database(s) on a NAS device means that you are storing them remote form the CDS.

Storing Project Databases in Server Storage

For the same reason, you can store a Cradle project database on a server that is separate from the computer that runs the CDS.

Therefore storing Cradle database(s) on a separate server means that you are storing them remote form the CDS.

Advantages and Disadvantages

If you don’t have a server in your network, then a NAS device is a cost-effective means to add centralised storage.

NAS devices are often used to collate data for backup. Storing Cradle databases on a NAS device eliminates the need to copy the databases onto the NAS device for backup.

Not storing Cradle databases on the machine that runs the CDS adds considerable latency between the CDS and the disk(s) that store the databases. Increasing latency worsens the performance of the CDS.

How to Store Cradle Databases on NAS

  1. Specify the location of the database’s directory using a UNC pathname, of the form: \\hostname\path
  2. Ensure that the access rights of the database’s NAS directory are accessible RW by the Windows ‘System’ user of the computer that runs the CDS or, for Linux, either root or whatever user your CDS runs as on its local machine

Recommendations

In general, we do not recommend storing project databases in NAS or indeed in any network-based resource.

If possible, only store your Cradle databases on the machine that runs the CDS and backup your databases regularly!