Tech Talks – DB2 for Linux, UNIX and Windows

by Sajan Kuttappa, Content Marketing Manager

IBM DB2 for Linux, UNIX and Windows database software is the foundation that powers many IBM Analytics offerings. In conjunction with the International DB2 Users Group (IDUG®), the DB2 product team hosts a series of monthly webinars highlighting key capabilities, use scenarios, and various aspects of data management needs. Below, you will find a listing of past webinars and upcoming topics. If there are topics that you would like us to cover, please email us at ibmdatamgmnt@gmail.com

2017
Topic Presenters
A Comparison of DB2 with New Capabilities in Oracle Database 12c and a look at SLOB (“Silly Little Oracle Benchmark”) Danny Arnold, Roger Sanders
Managing your DB2 on Cloud environment Kelly Schlamb
Extending SQL: Exploring the hidden JSON capabilities in DB2 George Baklarz
Jump Start 2017 with a new DB2 11.1 Matt Huras, Roger Sanders
2016
Topic Presenters
dashDB for Transactions – Fully managed Andrew Hilden
DB2 on the Cloud – Moving to the cloud with full control Jon Lind, Regina
BM DB2 on SAP – V11.1 Update and Recent Developments Karl Fleckenstein
DB2 Security: From the Data Center to the Cloud Roger Sanders
DB2 Tech Talk: Data Server Manager and DB2 connect Mike Connor, Anson Kokkat, Shilu Mathai
DB2 Tech Talk: DB2 V 11 performance update Peter Kokosielis
DB2 V11.1 Deep Dive on BLU & Analytics Enhancements John Hornibrook, David Kalmuk
Breaking scalability barriers: A DB2 V11.1 Technology Review Matt Huras / George Baklarz
DBaaS for Developers on IBM Cloud. Andrew Buckler
Can you use your SQL skills for big data? Paul Yip
What’s New in IBM Data Server Manager V1.1.2 Anson Kokkat

Network with peers at the Informix Users Group Conference 2017

rajesh

 

 

by Rajesh Govindan, Portfolio Marketing Manager – Informix

When we asked David Link from Nebraska what he liked most about the Informix Users Group Conference in 2016, he told us that networking was high on his list.

“I had an excellent time,” he said, “The content was great, the people were great, the social events were great…and it’s a great place to network and meet with other people.”

In other words, the IIUG Conference was great!

If you want to network with other professionals while you improve your Informix skills and learn about upcoming product enhancements, you’ll want to attend the 2017 event.

The International Informix Users Group conference will be held at the Marriott City Center, April 23 to April 27, 2017 in Raleigh, North Carolina, US. It will feature three full days of educational sessions for Informix DBAs, developers and managers, plus social events where you can meet with others who’ve encountered and resolved issues that you may be dealing with in your job.

The IIUG conference is the world’s largest gathering of Informix users, and attracted over 400 professionals last year from throughout the world. Enthusiastic participants from Bangalore to Omaha came, learned, and shared at an event geared specifically to their work and their information needs.

If you’d like to know more about this year’s IIUG event, visit our web site. Those who register before January 31st will save their company money with an Early Bird Special that offers $375 off the regular fee.

Also, if you attended last year’s event –like David Link –we’d love to know what you liked most about it. Please leave a comment to tell others about your experience.

 

Will you join me at the Informix Users Group Conference 2017?

rajesh

 

 

by Rajesh Govindan, Portfolio Marketing Manager – Informix

Are you interested in improving your Informix skills, learning about new features, and networking with others who have encountered –and resolved –the same challenges that you face? Do you want to become professionally certified on Informix or other IBM Analytics products? Would you like to attend seminars and tutorials that help you develop skills that will increase your value to your organization? Of course you do!

To give you a heads up , next year’s International Informix Users Group conference will be held April 23 to April 27, 2017 in Raleigh, North Carolina, US. There, at Marriott City Center, you’ll enjoy three full days of educational sessions for Informix DBAs, developers and managers. We’ll have several tracks dedicated to specific areas of learning, so you can select which one is best for you.

The IIUG conference is the world’s largest gathering of Informix users. Last year’s event attracted over 400 professionals from throughout the world, and garnered enthusiastic reviews from many who participated.

Hari Ammundi, Senior DBA at Action Net, attended for the first time in 2016, and told us, “The workshops are a wealth of information that I’m going to take back, and I think I’ll be visiting year after year for these functions.”   Hari, we look forward to seeing you again!

If you’d like to learn more about this year’s IIUG event, visit the web site. Those who register before January 31st will save their company money with an Early Bird Special that offers $375 off the regular fee.

Also, if you’ve got something to teach or talk about, we’re seeking presenters. So drop a note to Bruce Simms at bruce@iiug.org. (Presentations to global audiences look great on performance reviews and resumes!)

This is the premier world event for Informix DBAs, developers and managers. Please join   and together we can ensure IIUG members remain recognized for their professionalism, expertise, and commitment to learning new Informix skills.

 

 

(Did you attend the IIUG event last year? If so, leave a comments to let me know what you enjoyed most at the conference.)

 

Simply Powerful and Flexible – IBM Informix on Cloud

pradeep

 

 

by Pradeep Muthalpuredathe

To make sense of the avalanche of data generated today, you will need to put the data to work. The more you do it, the better the outcome. But the cost of storing and managing the data on premise grows very fast along with growing infrastructure and operational cost. Add to that the cost and challenge of scaling your requirements to match your needs – and pretty soon it all starts getting overwhelming.

IBM recently launched the Informix on Cloud, a hosted cloud service on IBM Bluemix, running on IBM SoftLayer Cloud infrastructure – just to address these challenges. Available in configurations (sizes) to suit your every business need, IBM Informix on Cloud builds on the tried and tested capabilities of Informix Advanced Enterprise server. Besides the OLTP and OLAP capabilities of Informix, you can take advantage of industry leading technologies like TimeSeries, Spatial, NoSQL, along with the high-performance, being delivered at a very low total cost of ownership.

You can have your current on premises applications connect to Informix on Cloud and continue your current operations while getting the benefits of Cloud. You can also build your analytics applications using other Bluemix services which will integrate seamlessly with Informix on Cloud, thus delivering data visualization, executive dashboards, predictive analytics and streaming analytics capabilities, all on the same platform. The table below will help describe this better –

Synergy Area Use Cases Description
Predictive Analytics Predictive analytics solutions for different industry use cases, including manufacturing, transportation, insurance, weather related use cases Predict and plan maintenance requirements, eliminate unplanned downtimes, avoid accidents/disasters, by using historic data to look into models and patterns, to predict behavior, potential failures/outages, etc..
Watson Analytics Data visualization, dashboards and infographics, automate predictive analytics Gain and share insights from the data in the Informix repositories, trigger actions based on events and find answers to business questions
Spark Analytics Complex in-memory (lightning fast) analytics from hybrid data stored in Informix, and take advantage of machine learning capabilities Spark Analytics provide big-data analytics capabilities for deeper analytics for data in Informix on Cloud, real-time insights on streaming data to Informix
Dataworks Move data from multiple sources to the Informix on Cloud service, cleanse and visualize data, to improve quality of data and deliver accurate insights Access to data from a hybrid source, a proven way to move data to Informix on Cloud

 

Streaming Analytics Real-time processing of massive amounts of unstructured and streaming time-series data to  Informix on Cloud service for operational data stores. Real-time analysis on data-in-motion, combined with TimeSeries analytics with Informix on Cloud

 

One of the most frequent concern/question about hosting data in the cloud is about security and privacy. With Informix on Cloud, your data is encrypted with industry standard algorithm, both while at rest in the database and in flight between the database and client applications.

Informix is uniquely positioned as the database of choice for Internet of Things (IoT) solutions – with its native support for time series, spatial and NoSQL data types. And its zero administration requirements, auto configuration and autonomic capabilities make it deeply embeddable in IoT edge analytic solution on gateway devices at the edge of IoT. With Informix on Cloud, you can now build a complete end-to-end IoT solution that combines advanced analytics in the cloud with edge analytics. You can take advantage of Informix’s industry leading replication capabilities and its in-memory query acceleration with Informix Warehouse Accelerator (IWA), to build a complete solution.

Whether you are a DBA, Application developer, CTO or CIO of your company, Informix on Cloud provides a simple, easy and secure option with a very low total cost of ownership (TCO), that more than meets your data management and analytics requirements. For more information, check out the Informix on Cloud web site .

DB2 for SAP – Poised for growth

800x400_minn

Like most organizations, your company is pressing ahead to close out projects and prepare new ones for the coming year. Inevitably, these annual evaluations and reviews call into question the preservation of your existing IT investment while exploiting new areas for growth. Some vendors strongly encourage vertically integrated solutions with a promise of seamless operation, but reality dictates a closer look at technology choices.

If you’re currently running an SAP environment with DB2 for Linux, UNIX and Windows software, there are quite a few options available to help you leverage existing DB2 infrastructure and be well positioned for new projects. You are cordially invited to attend a half-day seminar to learn key DB2 insights and considerations for SAP environments.

Our IBM SAP experts will:

  • Outline issues and factors affecting database use with SAP
  • Go over key considerations on selecting solutions
  • Provide a DB2 for Linux, UNIX and Windows Roadmap
  • Showcase breakthrough in-memory technologies
  • Illustrate how your continued use of DB2 for Linux, UNIX and Windows in SAP environment is not just safe but the optimal one for growth

The seminars will be held at 2 cities in North America. Please find below details and register for the one nearest to your city

  1. Cincinnati   – Monday, December 12th, 2016 from 9:30 AM –2:30 PM EST

http://ibm.biz/BdsqQv

     2. New York City –  Tuesday, December 13th, 2016 from 9.30 AM – 2.30 PM EST

 http://bit.ly/2fRPn1B

After all, wouldn’t you want to know how to make existing investment work smarter without the risks of rip and replace?

 

 

 

IBM DB2 sessions at IBM Insight at World of Watson conference

by Sajan Kuttappa,  Marketing Manager- IBM Analytics Platform

As organizations develop next-generation applications for the digital era, many are using cognitive computing ushered in by IBM Watson technology. To make the most of these next-generation applications, you need a next-generation database that must handle a massive volume of data while delivering high performance to support real-time analytics. At the same time, it must provide data availability for demanding applications, scalability for growth and flexibility for responding to changes

IBM DB2 enables you to meet these challenges by providing enterprise-class scalability while also leveraging adaptive in-memory BLU Acceleration technology to support the analytics needs of your business. DB2 also handles structured and semi-structured data from a variety of sources to provide deep insight. With the ability to support thousands of terabytes, you can use historic and current data to identify trends and make sound decisions. The new release DB2 11.1 that was announced earlier this year comes packed with many enhancements for BLU, OLTP, PureScale, security, SQL, and more!

Whether you are interested in an overview of the improvements available with the new release or an in-depth understanding of the new enhancements, IBM World of Watson is the place to be.  The IBM Insight conference is now part of IBM World of Watson 2016 on October 24-27 and continues to be the premiere industry event for data and analytics professionals, delivering unmatched value and exciting onsite opportunities to connect with peers, hear from thought leaders, experience engaging content, and receive training and certification.  This article will highlight the key DB2 sessions at the IBM World of Watson conference.

We will start with Session #3483 by Matt Huras, IBM DB2 Architect who will provide a technical overview of the new release and the value the new features provide for your installations. We also have the following sessions that provide deeper coverage for the new enhancements available with the new release

  • DB2 11.1 includes significant enhancements in the area of availability — particularly around the pureScale feature. You can attend the Session #1433 – “The Latest and Greatest on Availability and pureScale in DB2 11.1” to learn about these enhancements, including simplification of deployment, new operating system and virtualization options, HADR updates, and improvements in the areas of management and multitenancy.
  • DB2 11.1 packs several enhancements to protect your data whether they are on-premises or on the cloud. Do look out for Session #1038 – “DB2 Security: From the Data Center to the Cloud” for an overview of the various security mechanisms that are available with the latest version of DB2 for Linux, UNIX, and Windows, as well as introduce you to several things that must be taken into consideration if you plan on moving your DB2 database environment from the data center to the cloud.
  • There is a lot of talk about in-memory computing and columnar multi-partitioned databases to improve analytic query performance. DB2 1 brings MPP scale to BLU! If you need a detailed step-by-step approach to implement the newest version of DB2, come learn about often overlooked but very important best practices to understand before and after upgrading by attending the Session #1290– “Upgrading to DB2 with the Latest Version of BLU Acceleration”  
  • DB2 11.1 is the foundation for hybrid cloud database deployments. In addition to being available to install on cloud-based infrastructure it is also the foundation of DB2 on Cloud and dashDB cloud data service offerings. Attend the Session #1444 – “Hybrid Cloud Data Management with DB2 and dashDB” to learn more about these different options and when you’d want to choose one over another.
  • If you are deploying DB2 for SAP applications, we have lined up Session #2629 by SAP and IBM experts – “IBM DB2 on SAP – V11.1 Update and Recent Developments”.  In this session, we will give an overview of recent SAP on DB2 extensions and which DB2 V11.1 features are most important for SAP applications.  One of our clients – BCBS of TN will also share their experiences with DB2 V11.1 around analytics and the benefits that they’ve seen.

Our clients Nordea Group and Argonne National Laboratory will also share their experience with deploying IBM Data Server Manager.  The hands–on-labs HOL 1766B – “DB2 High Availability and Disaster Recovery with Single or Multiple Standby Databases” allows you to configure and manage a production database with single or multiple standby databases using DB2 HA/DR facilities.

If you are a new user of DB2, you can also read this guide to the introductory DB2 sessions . Whether you are determining your next move or optimizing your existing investments in data and analytics capabilities, the IBM World of Watson 2016 conference is the place for you. This is your opportunity to get the training, answers, certifications and insights you need to be at the top of your game . If you have not yet registered for the conference, we suggest you visit this link and register yourself  –  bit.ly/WorldofWatson 

IBM DB2 – the database for the cognitive era at IBM World of Watson 2016

sajan

 

 

by Sajan Kuttappa,  Marketing Manager- IBM Analytics Platform

IBM Insight, the premiere data, analytics and cognitive IBM conference, is now part of IBM World of Watson 2016 to be held at Las Vegas from October 24-27.  This year attendees will be able to experience first-hand a world of cognitive capabilities that IBM has been at the forefront of. World of Watson incorporates the kind of information you gained from IBM Insight — the tools and best practices to manage your data — and raises the game. You’ll also see how Watson’s capabilities give you a broad view of your business, its competitive landscape and what it takes to make your customers act. Our CEO – Ginni Rometty will deliver a keynote at this year’s conference. And on the evening of October 26th, our special event will feature Grammy winner Imagine Dragons.

Whether you’re a beginner or a seasoned DB2 professional, there is a treasure trove of information that you could walk away with. IBM experts and your peer speakers will share information about migration guidelines, new features of recent releases, implementation experiences, and much more. Likewise, our hands-on-labs (HOL) complement these topics to further enrich the experience.

For users new to DB2, we recommend attending session 3585 on “DB2 v11.1 Fundamentals” by Roger Sanders.  This presentation will provide a great overview of DB2 for Linux, UNIX and Windows. It will take attendees through the concepts covered on the DB2 11.1 Fundamentals certification exam: planning, security, working with databases and data objects, using SQL, and data concurrency. It will also provide a brief introduction to other DB2 based offerings like DB2 on Cloud and dashDB.

IBM provides number of database options for organizations who would like to deploy applications on the cloud – be it fully managed or hosted environment.  IBM dashDB for transactions provides a fully managed database service in the cloud that is optimized for online transaction processing workloads. DB2 on Cloud is a hosted service that offers the agility of cloud deployment and the management control you enjoy with the on-premises software.

  • If you would like to understand the capabilities of the dashDB for Transactions offering, consider attending session 3471 on “dashDB for Transactions: Fully Managed and Truly Awesome,” where we will discuss key features of this enterprise class service and its design and implementation for availability and performance.
  • DB2 on Cloud offering gives you everything you know and love about DB2 for Linux, UNIX and Windows software in a cloud environment hosted by IBM. You still have full DBA control to customize the database. You can rapidly provision it for instant productivity. And the monthly subscription-based licensing makes it easier to predict and control costs. As with any OLTP database supporting your critical applications, high availability and disaster recovery concerns are top of mind. We have lined up a session (Session #1439) that will help you understand how to “Implement High Availability and Disaster Recovery for DB2 on the Cloud.”

You can learn how to further optimize DB2 performance with management tools like IBM Data Server. The Hands-on-Lab  3141A- Secrets of the Pros: Using Data Server Manager to Monitor, manage and Mitigate Performance Problems will teach you how to use the latest version of IBM Data Server Manager to diagnose and resolve performance problems.

We hope that you can take advantage of these sessions by attending the World of Watson conference. Stay tuned for our next article on sessions for “Intermediate” skill sets and “Advanced” users.

We look forward to seeing you in Vegas. If you have not yet registered, please visit this link for more details – http://bit.ly/WorldofWatson

The value of common database tools and linked processes for Db2, DevOps, and Cloud

Michael

by Michael Connor, Analytics Offering Management

Today we released DB2 V11 for Linux, UNIX and Windows. The release includes updates to Data Server Manager (DSM) V2.1 and Data Server Driver connectivity V11 and Advanced Recovery Feature (ARF) V11.    As many of you may be aware of – 2 years ago we embarked on a strategy to completely rethink our tooling strategy.  The market was telling us we needed to focus more on a simplified user experience, a web console addressing both the power and casual user role, and deliver deep database support in support of production applications.  In  March 2015, we delivered our first iteration of Data Server Manager as part of 10.5.  This year we have yet again extended capability to this valuable platform and in addition extended support across a number of IBM Data stores including DB2, dashDB, DB2 on Cloud, and BigInsights.

First let’s talk about some of the drivers we hear related to Database Delivery.

  1. The LOB and LOB developer communities want access to mission critical data and extend that data through new customer facing OLTP applications.
  2. Business analysts are using more data than ever – in generating and enhancing customer value through Analytic applications.
  3. These new roles need on demand access to data across all aspects of the delivery lifecycle from idea inception to production delivery and support.
  4. While the timelines are lessened, the data expanded and the lifecycle speeded up, quality cannot suffer.

Therefore, the DBA, Development, Testing, and Production support roles are now participating in activities known as Continuous Delivery, Continuous Testing, and DevOps.  With the goal of improving customer service, decreasing cycle and delivery times, without decreasing quality.

DSM pic1Some areas that are addressed by our broader solutions for Continues Delivery, Testing, and DevOps include:

  • High Performance Unload of production data and selective data environment, including test data environment restore with DB2 Recovery Expert
  • Simplified test data management addressing discovery, subsetting, masking, and refresh with Test Data Management.
  • Automated driving of application test and performance based workloads with Rational Functional and Performance Tester.
  • Release Management and Deployment automation with Rational Urbancode.

And finally, areas improved with our latest DB2 releases

  • SQL Development and execution with Data Server Manager
  • Test and Deployment Data Server Monitoring with Data Server Manager
  • SQL capture and analysis with Data Server Manager
  • Client and application Data Access, Workload and Failover management with Data Server Drivers

DSM Pic 2The Benefits of considering a Continuous — Solution include reduced cycle times, lower risk of failure, improved application performance and reduced risk of downtime.

With the V11 Releases we have delivered enhancements including:

  • DSM: DB2 LUW V11 support  and monitoring improvements for PureScale applications, Extended Query history analysis
  • ARF: DB2 LUW V11 support and improvements for Analytics usage with BLU Acceleration
  • DS Driver (Also DB2 Connect): Manageability improvements, Performance enhancements, and extended driver support now for iMAC applications.

DSM Pic 3Many of the improvements noted above are also available for our private Cloud offering in preview DashDB Local – which leverages DSM as an integral component of their dashboard, and our public Cloud offering DB2 on Cloud.

Read more details about the announcement for further information:   http://www-01.ibm.com/common/ssi/ShowDoc.wss?docURL=/common/ssi/rep_ca/9/872/ENUSAP16-0139/index.html&lang=en&request_locale=en

Also check out the DB2 LUW Landing Page:  http://www.ibm.com/analytics/us/en/technology/db2/db2-linux-unix-windows.html

 

Blogger:    Michael Connor, with Analytics offering management, joined IBM in 2001 and has focused early in his IBM career on launching the z/OS Development Tooling business centered on Rational Developer for z.  Since moving to Analytics in 2013, Michael leads the team responsible for Core Database Tooling

Migrating a DB2 database from a Big Endian environment to a Little Endian environment

roger

By Roger Sanders, DB2 for LUW Offering Manager, IBM

What Is Big-Endian and Little-Endian?

Big-endian and little-endian are terms that are used to describe the order in which a sequence of bytes are stored in computer memory, and if desired, are written to disk. (Interestingly, the terms come from Jonathan Swift’s Gulliver’s Travels where the Big Endians were a political faction who broke their boiled eggs on the larger end, defying the Emperor’s edict that all eggs be broken on the smaller end; the Little Endians were the Lilliputians who complied with the Emperor’s law.)

Specifically, big-endian refers to the order where the most significant byte (MSB) in a sequence (i.e., the “big end”) is stored at the lowest memory address and the remaining bytes follow in decreasing order of significance. Figure 1 illustrates how a 32-bit integer would be stored if the big-endian byte order is used.

endian image1Figure 1. Big-endian byte order

For people who are accustomed to reading from left-to-right, big-endian seems like a natural way to store a string of characters or numbers; since data is stored in the order in which it would normally be presented, programmers can easily read and translate octal or hexadecimal data dumps. Another advantage of using big-endian storage is that the size of a number can be more easily estimated because the most significant digit comes first. It is also easy to tell whether a number is positive or negative—this information can be obtained by examining the bit at offset 0 in the lowest order byte.

Little-endian, on the other hand, refers to the order where the least significant byte (LSB) in a sequence (i.e., the “little end”) is stored at the lowest memory address and the remaining bytes follow in increasing order of significance. Figure 2 illustrates how the same 32-bit integer presented earlier would be stored if the little-endian byte order were used.

endian image 2

 Figure 2. Little-endian byte order

One argument for using the little-endian byte order is that the same value can be read from memory, at different lengths, without having to change addresses—in other words, the address of a value in memory remains the same, regardless of whether a 32-bit, 16-bit, or 8-bit value is read. For instance, the number 12 could be read as a 32-bit integer or an 8-bit character, simply by changing the fetch instruction used. Consequently, mathematical functions involving multiple precisions are much easier to write.

Little-endian byte ordering also aids in the addition and subtraction of multi-byte numbers. When performing such operations, the computer must start with the least significant byte to see if there is a carry to a more significant byte—much like an individual will start with the rightmost digit when doing longhand addition to allow for any carryovers that may take place. By fetching bytes sequentially from memory, starting with the least significant byte, the computer can start doing the necessary arithmetic while the remaining bytes are read. This parallelism results in better performance; if the system had to wait until all bytes were fetched from memory, or fetch them in reverse order (which would be the case with big-endian), the operation would take longer.

IBM mainframes and most RISC-based computers (such as IBM Power Systems, Hewlett-Packard ProLiant servers, and Oracle SPARC servers) utilize big-endian byte ordering. Computers with Intel and AMD processors (CPUs) use little-endian byte ordering instead.

It is important to note that regardless of whether big-endian or little-endian byte ordering is used, the bits within each byte are usually stored as big-endian. That is, there is no attempt to reverse the order of the bit stream that is represented by a single byte. So, whether the hexadecimal value ‘CD’ for example, is stored at the lowest memory address or the highest memory address, the bit order for the byte will always be: 1100 1101

Moving a DB2 Database To a System With a Different Endian Format

One of the easiest ways to move a DB2 database from one platform to another is by creating a full, offline backup image of the database to be moved and restoring that image onto the new platform. However, this process can only be used if the endianness of the source and target platform is the same. A change in endian format requires a complete unload and reload of the database, which can be done using the DB2 data movement utilities. Replication-based technologies like SQL Replication, Q Replication, and Change Data Capture (CDC), which transform log records into SQL statements that can be applied to a target database, can be used for these types of migrations as well. On the other hand, DB2 High Availability Disaster Recovery (HADR) cannot be used because HADR replicates the internal format of the data thereby maintaining the underlying endian format.

The DB2 Data Movement Utilities (and the File Formats They Support)

DB2 comes equipped with several utilities that that can be used to transfer data between databases and external files. This set of utilities consists of:

  • The Export utility: Extracts data from a database using an SQL query or an XQuery statement, and copies that information to an external file.
  • The Import utility: Copies data from an external file to a table, hierarchy, view, or nickname using INSERT SQL statements. If the object receiving the data is already populated, the input data can either replace or be appended to the existing data.
  • The Load utility: Efficiently moves large quantities of data from an external file, named pipe, device, or cursor into a target table. The load utility is faster than the Import utility because it writes formatted pages directly into the database, instead of performing multiple INSERT
  • The Ingest utility: A high-speed, client-side utility that streams data from files and named pipes into target tables.

Along with these built-in utilities, IBM InfoSphere Optim High Performance Unload for DB2 for Linux, UNIX and Windows, an add-on tool that must be purchased separately, can be used to rapidly unload, extract, and repartition data in a DB2 database. Designed to improve data availability, mitigate risk, and accelerate database migrations, this tool helps DBAs work with very large quantities of data with less effort and faster results.

Regardless of which utility is used, data can only be written to or read from files that utilize one of the following formats:

  • Delimited ASCII
  • Non-delimited or fixed-length ASCII
  • PC Integrated Exchange Format
  • Extensible Markup Language (IBM InfoSphere Optim High Performance Unload for DB2 for Linux, UNIX and Windows only.)

Delimited ASCII (DEL)

The delimited ASCII file format is used by a wide variety of software applications to exchange data. With this format, data values typically vary in length, and a delimiter, which is a unique character not found in the data values themselves, is used to separate individual values and rows. Actually, delimited ASCII format files typically use three distinct delimiters:

  • Column delimiters. Characters that are used to mark the beginning or end of a data value. Commas (,) are typically used as column delimiter characters.
  • Row delimiters. Characters that are used to mark the end of a single record or row. On UNIX systems, the new line character (0x0A) is typically used as the row delimiter; on Windows systems, the carriage return/linefeed characters (0x0D–0x0A) are normally used instead.
  • Character delimiters. Character that are used to mark the beginning and end of character data values. Single quotes (‘) and double quotes (“) are typically used as character delimiter characters.

Typically, when data is written to a delimited ASCII file, rows are streamed into the file, one after another. The appropriate column delimiter is used to separate each column’s data values, the appropriate row delimiter is used to separate each individual record (row), and all character and character string values are enclosed with the appropriate character delimiters. Numeric values are represented by their ASCII equivalent—the period character (.) is used to denote the decimal point (if appropriate); real values are represented with scientific notation (E); negative values are preceded by the minus character (-); and positive values may or may not be preceded by the plus character (+).

For instance, if the comma character is used as the column delimiter, the carriage return/line feed character is used as the row delimiter, and the double quote character is used as the character delimiter, the contents of a delimited ASCII file might look something like this:

10,”Headquarters”,860,”Corporate”,”New York”

15,”Research”,150,”Eastern”,”Boston”

20,”Legal”,40,”Eastern”,”Washington”

38,”Support Center 1″,80,”Eastern”,”Atlanta”

42,”Manufacturing”,100,”Midwest”,”Chicago”

51,”Training Center”,34,”Midwest”,”Dallas”

66,”Support Center 2″,112,”Western”,”San Francisco”

84,”Distribution”,290,”Western”,”Denver”

Non-Delimited ASCII (ASC)

With the non-delimited ASCII file format, data values have a fixed length, and the position of each value in the file determines which column and row a particular value belongs to.

When data is written to a non-delimited ASCII file, rows are streamed into the file, one after another and each column’s data value is written using a fixed number of bytes. (If a data value is smaller that the fixed length allotted for a particular column, it is padded with blanks.) As with delimited ASCII files, a row delimiter is used to separate each individual record (row) — on UNIX systems the new line character (0x0A) is typically used; on Windows systems, the carriage return/linefeed characters (0x0D–0x0A) are used instead. Numeric values are treated the same as when they are stored in delimited ASCII format files.

Thus, a simple non-delimited ASCII file might look something like this:

10Headquarters       860Corporate   New York

15Research                150Eastern          Boston

20Legal                        40 Eastern         Washington

38Support Center   180Eastern        Atlanta

42Manufacturing    100Midwest       Chicago

51Training Center   34 Midwest       Dallas

66Support Center   211Western        San Francisco

84Distribution         290Western        Denver

 

PC Integrated Exchange Format (IXF)

The PC Integrated Exchange Format file format is a special file format that is used almost exclusively to move data between different DB2 databases. Typically, when data is written to a PC Integrated Exchange Format file, rows are streamed into the file, one after another, as an unbroken sequence of variable-length records. Character data values are stored in their original ASCII representation (without additional padding), and numeric values are stored as either packed decimal values or as binary values, depending upon the data type used to store them in the database. Along with data, table definitions and associated index definitions are also stored in PC Integrated Exchange Format files. Thus, tables and any corresponding indexes can be both defined and populated when this file format is used

Extensible Markup Language (XML)

Extensible Markup Language (XML) is a simple, yet flexible text format that provides a neutral way to exchange data between different devices, systems, and applications. Originally designed to meet the challenges of large-scale electronic publishing, XML is playing an increasingly important role in the exchange of data on the web and throughout companies. XML data is maintained in a self-describing format that is hierarchical in nature. Thus, a very simple XML file might look something like this:

<?xml version=”1.0″ encoding=”UTF-8″ ?>

<customerinfo>

<name>John Doe</name>

<addr country=”United States”>

<street>25 East Creek Drive</street>

<city>Raleigh</city>

<state-prov>North Carolina</state-prov>

<zip-pcode>27603</zip-pcode>

</addr>

<phone type=”work”>919-555-1212</phone>

<email>john.doe@xyz.com</email>

</customerinfo>

As noted earlier, only IBM InfoSphere Optim High Performance Unload for DB2 for Linux, UNIX and Windows can work with XML files.

db2move and db2look

As you might imagine, the Export utility, together with the Import utility or the Load utility, can be used to copy a table from one database to another. These same tools can also be used to move an entire database from one platform to another, one table at a time. But a more efficient way to move an entire DB2 database is by using the db2move utility. This utility queries the system catalog of a specified database and compiles a list of all user tables found. Then it exports the contents and definition of each table found to individual PC Integrated Exchange Format (IXF) formatted files. The set of files produced can then be imported or loaded into another DB2 database on the same system, or they can be transferred to another server and be imported or loaded to a DB2 database residing there.

The db2move utility can be run in one of four different modes: EXPORT, IMPORT, LOAD, or COPY. When run in EXPORT mode, db2move utilizes the Export utility to extract data from a database’s tables and externalize it to a set of files. It also generates a file named db2move.lst that contains the names of all of the tables that were processed, along with the names of the files that each table’s data was written to. The db2move utility may also produce one or more message files containing warning or error messages that were generated as a result of the Export operation.

When run in IMPORT mode, db2move uses the file db2move.lst to establish a link between the PC Integrated Exchange Format (IXF) formatted files needed and the tables into which data is to be populated. It then invokes the Import utility to recreate each table and their associated indexes using information stored in the external files.

And, when run in LOAD mode, db2move invokes the Load utility to populate tables that already exist with data stored in PC Integrated Exchange Format (IXF) formatted files. (LOAD mode should never be used to populate a database that does not already contain table definitions.) Again, the file db2move.lst is used to establish a link between the external files used and the tables into which their data is to be loaded.

Unfortunately, the db2move utility can only be used to move table and index objects. And if the database to be migrated contains other objects such as aliases, views, triggers, user-defined data types (UDTs), user-defined functions (UDFs), and stored procedures, you must duplicate those objects in the target database as well. That’s where the db2look utility comes in handy. When invoked, db2look can reverse-engineer an existing database and produce a set of Data Definition Language (DDL) SQL statements that can then be used to recreate all of the data objects found in the database that was analyzed. The db2look utility can also collect environment registry variable settings, configuration parameter settings, and statistical (RUNSTATS) information, which can be used to duplicate a DB2 environment on another system.

 

DB2 Direct: A new way of consuming your Database

headshots 012

by Phillip Downey, WW program Director, IBM Analytics Platform Hybrid Cloud Strategy

 

In DB2 11.1, we introduced two new and easy to consume DB2 Direct editions: DB2 Direct Advanced and DB2 Direct Standard. Both editions bring a new dimension to the database offerings for the small and larger enterprise clients that are looking for the flexibility and scalability of the hybrid cloud. They can be acquired directly online via passport advantage and offer a simplified licensing metric and monthly subscription pricing model that are ideal for private, public and hybrid cloud deployments.

Packaging

·        DB2 Direct Advanced Edition

The DB2 Direct Advanced Edition has all DB2 Server and Client features from DB2 Advanced Server Edition including encryption, multitenant deployments, adaptive compression, BLU Acceleration, SQL compatibility with PL/SQL, Data Server Manager, pureScale and database partitioning feature options. It also includes federation capabilities providing access to non-DB2 database sources like Oracle, MS SQL, Teradata, Hadoop, Netezza, Spark and other solutions.

Advanced Federation Capabilities

Phil blog image

 

 

 

 

 

It also includes access to 10 User licenses of Infosphere Data Architect per installation for designing and deploying database implementations.

·        DB2 Direct Standard Edition

DB2 Direct Standard Edition is modelled on DB2 Workgroup Edition, which provides encryption, pureScale for Continuously available HA deployments, Multitenant Deployments, SQL compatibility with PL/SQL, Data Server Manager Base Edition, Table partitioning, multi-dimensional clustering, parallel query and concurrent Connection pooling. It is limited to 16 cores and 128GB of RAM and is ideal for small to mid-sized database applications providing enterprise level availability, Query performance and Security as well as unlimited database size

You can take advantage of the new subscription model to lower costs and enjoy licensing flexibility for on-premises and cloud deployments:

Licensing Metrics:

Virtual ProcessorCore (VPC) Charge metric

  • Virtual processor core licensing gives you flexibility and simplified sub capacity licensing options that enables you to optimize your licensing to meet your business requirements.
  • There are two Licensing Scenarios you can apply
    • Simply license the sum of all available Virtual Processor Cores on all Virtual Servers the Direct edition is installed on
    • OR when you can identify a Server and it is more cost effective to do so simply license all available Processor Cores on the Physical Server regardless of the number of virtual machines on the system.
  • Benefits: This makes it simple for private and public Cloud deployments alike and enables you to optimise your licensing

Pricing Structure:

Subscription based pricing

      • DB2 Direct Advanced $354 USD per month per VPC
      • DB2 Standard Edition $135 USD per month per VPC

(Prices as of May 10th, 2016 in the United States.)

Each Deployment requires a minimum of 2 VPCs except in the case of Warm standby, which requires only one VPC.

These editions are ideal for customers who want to move to a subscription based model on their private cloud or a 3rd party vendors (hosts) and pay as their applications grow in size. It is also ideal for ISV’s who charge their applications to customers on a subscription model and want an easy to order database at competitive subscription pricing.

Understanding the Virtual Process Core Metric

Virtual Processor Cores are defined to simplify licensing in the private or public cloud deployment environment. You can deploy DB2 licenses with confidence even though you may or may not be aware of the underlying infrastructure. It enables customers to easily analyze their Licensing requirements including in sub-capacity situations.

A Virtual Processor Core is a Processor Core in an unpartitioned Physical Server, or a virtual core assigned to a Virtual Server.  The Licensee must obtain entitlement for each Virtual Processor Core made available to the Program.

For each Physical Server, the Licensee must have sufficient entitlements for the lesser of

  1. the sum of all available Virtual Processor Cores on all Virtual Servers made available to the Program or
  2. all available Processor Cores on the Physical Server.

Other key Virtual Processor Core considerations for you to understand

    • If the number of VPCs is greater than the physical cores, then you only need to license the number of physical cores on the machine
    • Minimum of 2 VPCs per deployment (1 VPC for idle/warm standby)

You can determine the VPC requirement through DB2 Itself by executing the following on each Physical or logical server DB2 is installed on and take the Online CPU Count and divided it by the HMTdegree result (threading degree) to get the count of Virtual CPU’s present.

“Db2pd –osinfo”

An example of this In a Cloud deployment

  • A customer buys a Virtual Cloud Server as a Service on a internal private cloud or MSP like Softlayer/Azure/ Amazon/rackspace ….
  • They purchase an 8 core Virtual CPU Environment
  • The customer runs “ Db2pd –osinfo” is run on the machine and shows HMTDegree of 1 and OnlineCPU of 8

The customer must license 8 VPC for this environment

An Example of a Private Cloud deployment using VM-Ware

  • A customer deploys Multiple VMWare Hosts are created on a server to run DB2. The server is a 2 Socket server, 8 cores per processor, with hyper-threading turned on to a degree of 2 (16 physical cores) Each of the 11 virtual VMs deployed Reports 6 Virtual Processors.
  • The Customer runs “db2pd –osinfo” across all VMWare Hosts reporting a total of Online CPU of 64 across 11 Virtual Machines (HMTDegree of 1 for all VMs)

As the Hardware can be physically Identified as a 16 core server the customer only has to pay for 16 VPC’s not 64 as some competitor programs would as it is the lesser of the two numbers.

Stay tuned for more information around the enhancements that DB2 v 11.1 comes with.  You may also want to attend the upcoming webinar on June 14th to learn how to maximize your data infrastructure investments. Register here http://bit.ly/v11launchwebcast