Use and Advantages of Apache Derby DB


Developed by Apache Software Foundation, Apache Derby DB is a completely free, open-source relational database system developed purely with Java. It has multiple advantages that make it a popular choice for Java applications requiring small to medium-sized databases.

Reliable and Secure

With over 15 years in development, Derby DB had time to grow, add new and improve on the existing components. Even though it has an extremely small footprint – only 3.5MB of all JAR files – Derby is a full-featured ANSI SQL database, supporting all the latest SQL standards, transactions, and security factors.

The small footprint adds to its versatility and portability – Derby can easily be embedded into Java applications with almost no performance impact. It’s extremely easy to install and configure, requiring almost no administration afterward. Once implemented, there is no need to further modify or set up the database at the end user’s computer. Alongside the embedded framework, Derby can also be used in a more familiar server mode.

All documentation containing different manuals for specific versions of Derby can be found on their official website, at :

Cross-Platform Support

Java is compatible with almost all the different platforms, including Windows, Linux, and MacOS. Since Derby DB is implemented completely in Java, it can be easily transferred without the need for different distribution downloads. It can use all types of Java Virtual Machines as long as they’re properly certified. Apache’s Derby includes the Derby code without any modification to the elemental source code.

Derby supports transactions, which are executed for quick and secure data retrieval from the database as well as referential integrity. Even though the stored procedures are made in Java, in the client/server mode Derby can bind to PHP, Python and Perl programming languages. 

All data is encrypted, with support for database triggers to maintain the integrity of the information. Alongside that, custom made functions can be created with any Java library so the users can manipulate the data however they want. 

Embedded and Server Modes

Derby’s embedded mode is usually recommended as a beginner-friendly option. The main differences are in who manages the database along with how it’s stored. 

When Derby is integrated as a whole and becomes a part of the main program, it acts as a persistent data store and the database is managed through the application. It also runs within the Java Virtual Machine of the application. In this mode, no other user is able to access the database – only the app that it is integrated into. As a result of these limits, the embedded mode is most useful for single-user apps.

If it’s run in server mode, the user starts a Derby network server which is tasked with responding to database requests. Derby runs in a Java Virtual Machine that hosts the server. The database is loaded onto the server, waiting for client applications to connect to it. This is the most typical architecture used by most of the other bigger databases, such as MySQL. Server mode is highly beneficial when more than one user needs to have access to the database across the network.

Downloading Derby

Derby has to be downloaded and extracted from the .zip package before being used. Downloads can be found at the Apache’s official website:

Numerous download options are presented on there, depending on the Java version that the package is going to be used with. 

Using Derby requires having Java Development Kit (JDK) pre-installed on the system and then configuring the environment to use the JDBC driver. Official tutorials can be found at:

Running and Manipulating Derby DB

Interacting with Derby is done through the use of ‘ij’ tool, which is an interactive JDBC scripting program. It can be used for running interactive queries and scripts against a Derby database. The ij tool is run through the command shell.

The initial Derby connection command differs depending on whether it’s going to be run in embedded or server mode.

For a tutorial on how to use the connect commands, check out https://www.vogella.com/tutorials/ApacheDerby/article.html.

Some Useful Derby DB Documentation

Derby Reference Manual‎: ‎

Derby Server and Administration Guide‎: ‎

API Reference‎:

Derby Tools and Utilities Guide‎: ‎

ij Basics

In conclusion, Derby DB is a lightweight yet efficient tool that can be integrated into various types of Java applications with ease.

DataStage – Timestamp from Datetime

Timestamp from date is one of those data type conversions, which I occasionally have to do in DataStage but can never seem to remember. So, I thought I would write this quick post to document the data type conversion code, which is easy, once I finally remember how to do it again. 

The TimestampFromDateTime Function

I use the TimestampFromDateTime(%date%,%time%) function to do this data type conversion. I’m sure there are other ways to achieve the result, but I find this method clean and easy to perform. The TimestampFromDateTime(%date%,%time%) function is in the Functions > Date & Time menu.

To populate the function, you need only add your date field on use ’00:00:00’ as your time element

Time Element example

TimestampFromDateTime(<<Date Field Here>>, ’00:00:00′)

Example transformer code With Date Field

TimestampFromDateTime(Lnk_10.POSTED_DATE, ’00:00:00′)

Infosphere Knowledge Resources

IBM Knowledge Center

The IBM Knowledge Center is the IBM product documentation Site designed to help you plan, install, configure, use, tune, monitor, troubleshoot, and maintain the product.

IBM InfoSphere Information Server Version 11.7.0 documentation

The IBM InfoSphere Information Server Version 11.7.0 documentation provides IIS specific documentation about how to install and use InfoSphere Information Server

IBM Support Portal

The IBM Support Portal is IBM’s technical support resource for all IBM products and services including downloads, fixes, drivers, APARs, product documentation, Redbooks, whitepapers and more.

IBM InfoSphere Information Server Content Hub

The IBM InfoSphere Information Server content hub provides additional on Information Server components including documentation, blog entries, videos, and more.

IBM developerWorks

The IBM InfoSphere Information Server content hub provides additional on Information Server components including documentation, blog entries, videos, and more.

Related Reference

DataStage – Timestamp from date

This is one of those data type conversions which I occasionally have to do in DataStage, but can never seem to remember. So, I thought I would write this quick post to document  the data type conversion code, which is really easy, once I finally remember how to do it again. 

The TimestampFromDateTime  Function

I use the TimestampFromDateTime function to do this data type conversion. I’m sure there are other ways to achieve the result, but I find this method clean and easy to perform. The TimestampFromDateTimefunction is in the Functions > Date & Time menu.

To populate the function, you need only add your date field on use ’00:00:00’ as your time element


TimestampFromDateTime Function Format


TimestampFromDateTime(%date%,%time%)


TimestampFromDateTime with Time Element example

TimestampFromDateTime(<<Date Field Here>>, ’00:00:00′)

Example transformer code With Date Field

TimestampFromDateTime(Lnk_10.POSTED_DATE, ’00:00:00′)

Related References

What is cloud computing? 

Cloud computing is a service driven model for enabling ubiquitous, convenient, on demand network access to a shared pool computing resources that can be rapidly provisioned and released with minimal administrative effort or service provider interaction.

Related References

How the IBM Common SQL Engine (CSE) Improves DB2

Common SQL Engine (CSE)
Common SQL Engine (CSE)

Today, newfound efficiencies and innovation are key to any business success – small, medium or large. In the rapidly evolving field of data analytics, innovative approaches to handling data are particularly important since data is the most valuable resource any business can have. IBM common SQL Engine is delivering application and query compatibility that is allowing companies to turn their data into actionable insights. This is allowing businesses to unleash the power of their databases without constraints.

But, is this really important?

Yes. Many businesses have accumulated tons of data over the years. This data resides in higher volumes, more locations throughout an enterprise – on-premise and on-cloud –, and in greater variety. Typically, this data should be a huge advantage, providing enterprises with actionable insights. But, often, this doesn’t happen.

IBM Hybrid Data Management.

With such a massive barrel of complex legacy data, many organizations find it confusing to decide what to do with it. Or where to start. The process of migrating all that data into new systems is simply a non-starter. As a solution, enterprises are turning to IBM Db2 – a hybrid, intuitive data approach that marries data and analytics seamlessly. IBM Db2 hybrid data management allows flexible cloud and on-premises deployment of data.

However, such levels of flexibility typically require organizations to rewrite or restructure their queries, and applications that will use the diverse, ever-changing data. These changes may even require you to license new software. This is costly and unfeasible. To bridge this gap, the Common SQL Engine (CSE) comes into play.

How IBM Common SQL Engine is Positioning Db2 for the Future?

The IBM Common SQL Engine inserts a single layer of data abstraction at the very data source. This means that, instead of migrating the data all at once, you can now apply data analytics wherever the data resides – whether on private, public or hybrid cloud – by using the Common SQL Engine as a bridge.

The IBM’s Common SQL Engine provides portability and consistency of SQL commands, meaning that the SQL is functionally portable across multiple implementations. It allows seamless movement of workloads to the cloud and allows for multiplatform integration and configurations regardless of their programming language.

Ideally, the Common SQL Engine is supposed to be the heart of the query and the foundation of application compatibility. But it does so much more!

Its compatibility extends beyond data analytic applications to include security, management, governance, data management, and other functionalities as well.

How does this improve the quality, flexibility, and portability of Db2?

By allowing for integration across multiple platforms, workloads and programming languages, the Common SQL Engine, ultimately, leads to a “data without limits” environment for Db2 hybrid data management family through:

  1. Query and application compatibility

The Common SQL engine (CSE) ensures that users can write a query, and be confident that it will work across the Db2 hybrid data management family of offerings. With the CSE, you can change your data infrastructure and location – on-cloud or on-premises – without having to worry about license costs and application compatibility.

  1. Data virtualization and Integration

The common SQL engine has a built-in data virtualization service that ensures that you can access your data from all your sources. These services position Db2 family of offerings including, IBM Db2 warehouse, IBM Db2, IBM Db2 BigSQL amongst others.

This services also applies to IBM Integrated Analytics System, Teradata, Oracle, Puredata and Microsoft SQL server. Besides, you can work seamlessly with open-source solutions such as HIVE; and cloud sources such as Amazon Redshift. Such levels of integration are unprecedented!

By allowing users to effectively pull data from Db2 data stores and integrate it with data from non-IBM stores using a single query, the common SQL engine places Db2 at an authoritative position as compared to other data stores.

  1. Flexible Licensing

Licensing is one of the hardest nuts to crack, especially for smart organizations who rely on technologies such as the cloud to deliver their services. While application compatibility and data integration will save you time, flexible licensing saves you money, on the spot.

IBM’s common SQL engine allows flexible licensing, meaning that you can purchase one license model and deploy it whenever needed, or as your data architecture evolves. Using IBM’s FlexPoint licensing, you can purchase FlexPoints and use them across all Db2 data management offerings. This is a convenience in one place.

The flexible licensing will not only simplify the adoption and exchange of platform capabilities, but it also positions your business strategically by making it more agile. Your data managers will be able to access the tools needed on the fly, without going through a lethargic and tedious procurement process.

IBM Db2 Data Management Family Is Supported by Common SQL Engine (CSE) .

IBM Db2 is a family of custom, deployable database that allows enterprises to leverage existing investments. IBM Db2 allows businesses to use any type of data from an either structured or unstructured database (or data warehouse). It provides the right data foundation/environment with industry-leading data compression, on-premise and cloud deployment options, modern data security, robust performance for mixed loads and the ability to adjust and scale without redesigning.

The IBM Db2 family enable businesses to adapt, scale quickly and remain competitive without compromising security, risk levels or privacy. It features:

  • Always-on availability
  • Deployment and flexibility: On-premises, scale-on demand, and private or cloud deployments• Compression and performance
  • Embedded IoT technology is allowing businesses to act fast on the fly.

Some of these Db2 family offerings that are supported by the common SQL engine include:

  • Db2 Database
  • Db2 Hosted
  • Db2 Big SQL
  • Db2 on Cloud
  • Db2 Warehouse
  • Db2 Warehouse on Cloud
  • IBM Integrated Analytics System (IIAS)

Db2 Family Offerings and Beyond

Since the common SQL engine mainly focuses on data federation and propensity, other non-IBM databases can as well plug into the engine for SQL processing. These other 3rd party offerings include:

  • Watson Data Platform
  • Oracle
  • Hadoop
  • Microsoft SQL Server
  • Teradata
  • Hive

Conclusion

IBM Common SQL engine is allowing organizations to fully use data analytics to future-proof their business, and as well remain agile and competitive. In fact, besides the benefits of having robust tools woven into CSE, this SQL engine offers superior analytics and machine-learning positioning. Data processing can now happen at the speed of light –- 2X to 5X faster. The IBM Common SQL engine adds important capabilities to Db2, including freedom of location, freedom of use, and freedom of assembly.

Related References

An Overview of DB2 Federation

DB2 Federation
DB2 Federation

Data analytics has changed where data is no longer manageable in relational databases only. Data is flowing from various sources which are not of the same format. This means it is not possible to store all data in the same repository. Some are best suited for storing in relational databases, others for Apache Hadoop while others are best suited for NoSQL databases.

During data analyzing, so much time is taken in trying to bring the distributed data together instead of obtaining insights. Db2 Federation has come to the rescue of data analysts. Federation concept in db2 eliminates the need for storing data in different repositories and reduces the hassle of getting insights.

What is DB2 Federation?

DB2 federation is a data integration technology that permits remote database objects to be accessed as local DB2 database objects. This technology connects multiple databases and makes them appear like one database.

How does DB2 federation work?

Federation allows you to access all of your data that is on multiple distributed databases using a single query. When implemented in an organization, this technology can be used to access data that is on any of the organization’s Db2, whether local or in the cloud.

Why use DB2 federation?

So, why should you use the federation? This concept brings data of all formats into one virtual source. With data being retrieved from one virtual source, analyzing it becomes cost-effective and efficient.

What are its primary use cases for DB2 federation?

Merging of various sources of data

DB2 federation facilitates consolidating of data from sources data local and cloud to form one virtual data source. This eliminates the process of migrating data which can be expensive and troublesome.

Increase the capacity of a repository beyond the fixed limits

Physical storage capacity is bound to have a limit which is one reason you may find an organization has distributed its data in various repositories. With federation, the storage is virtual and therefore doesn’t have any limit. This technology can greatly help you if your physical dataset is running low on space.

Linking up to Db2 Warehouse on Cloud

People who use Db2 products can federate data from Db2 on Cloud and Db2 Warehouse on the Cloud. This will give them a joint interface where they can access, add, query, and analyze data without encountering the complex ETL processes. Better still, no additional code will be required to execute all these processes. This makes it easy for people with the low technical know-how to use these products smoothly.

Split data across different servers

At times, you might choose to partition your data. With federation integration technology, partitioned data can be queried with a unified interface. Federation allows you to better balance your workloads, scale precise parts of an app, and create micro-services that work harmoniously.

Generally, db2 federation makes it access data by bringing it together into a single virtual source. This brings about cost and time-saving benefits. When you want to analyze data, you can get insights immediately instead of spending a lot of time querying through repositories.

Related References