Unlocking Success with Data Integration: A Case Study of "Dress4Less"

 

In the modern retail landscape, companies face numerous challenges, from managing vast amounts of data to ensuring seamless operations across various departments. One key solution that has emerged to address these challenges is data integration. This article delves into the benefits of data integration using the example of a fictitious retail giant, "Dress4Less," a company similar to industry titans like Walmart or Target.

Understanding Data Integration

Data integration involves combining data from different sources into a unified view, enabling businesses to leverage this consolidated data for better decision-making, improved efficiency, and enhanced customer experiences. The process typically involves data ingestion, transformation, and storage, ensuring that disparate data sets from various sources can work together harmoniously.

The Dress4Less Scenario

Imagine Dress4Less, a retail giant with hundreds of stores across the country, each generating massive amounts of data daily. This data includes sales transactions, inventory levels, customer preferences, supply chain logistics, and more. Before embracing data integration, Dress4Less faced significant challenges:

1. Data Silos: Different departments (sales, marketing, inventory, etc.) operated in silos, with each team maintaining its own data repositories. This made it difficult to gain a holistic view of the business.

2. Inefficient Operations: Without integrated data, tasks like restocking, promotional planning, and customer service were often inefficient and error-prone.

3. Customer Experience: Lack of integrated customer data led to generic marketing campaigns and poor personalization, affecting customer satisfaction and loyalty.

 

The Benefits of Data Integration for Dress4Less

 

By adopting a robust data integration strategy, Dress4Less was able to transform its operations and achieve several key benefits:

 1. Enhanced Decision-Making

With integrated data, Dress4Less executives gained real-time insights into various aspects of the business. They could analyze sales trends, customer behavior, and inventory levels in a unified dashboard. This enabled data-driven decision-making, allowing the company to respond quickly to market changes and customer demands.

Example: During the holiday season, integrated data revealed a spike in demand for winter coats in the northeastern region. Dress4Less quickly adjusted inventory levels and marketing efforts to capitalize on this trend, resulting in increased sales and customer satisfaction.

2. Improved Operational Efficiency

Data integration streamlined Dress4Less' operations by automating processes and reducing manual interventions. For instance, integrated inventory data allowed the company to optimize restocking processes, ensuring that popular items were always available while minimizing overstock of less popular products.

Example: The integration of sales and inventory data enabled Dress4Less to implement an automated restocking system. This system used predictive analytics to forecast demand and trigger timely reorders, reducing stockouts and excess inventory.

3. Personalized Customer Experiences

Integrated customer data allowed Dress4Less to create personalized marketing campaigns and enhance the overall shopping experience. By analyzing purchase history, preferences, and behavior, the company could tailor promotions and recommendations to individual customers.

Example: Dress4Less launched a loyalty program that used integrated customer data to offer personalized discounts and product recommendations. Customers received notifications about sales on items they had previously shown interest in, leading to higher engagement and repeat purchases.

4. Streamlined Supply Chain Management

Data integration improved Dress4Less' supply chain management by providing end-to-end visibility into the entire process. The company could track shipments, monitor supplier performance, and identify potential bottlenecks in real-time.

Example: By integrating data from suppliers, warehouses, and stores, Dress4Less identified delays in the supply chain that were affecting product availability. The company worked with suppliers to address these issues, ensuring a smoother and more reliable supply chain.

5. Comprehensive Analytics and Reporting

Integrated data allowed Dress4Less to perform comprehensive analytics and generate detailed reports. This provided valuable insights into various aspects of the business, from sales performance to customer satisfaction, enabling continuous improvement.

Example: Dress4Less' marketing team used integrated data to analyze the effectiveness of different promotional campaigns. By comparing sales data with marketing efforts, they identified which campaigns drove the most revenue and adjusted their strategies accordingly.

Implementing Data Integration at Dress4Less

The journey to data integration for Dress4Less involved several key steps:

1. Identifying Data Sources

Dress4Less began by identifying all the data sources within the organization, including sales transactions, inventory records, customer databases, supplier information, and more. This comprehensive inventory of data sources was crucial for the integration process.

2. Choosing the Right Integration Tools

The company selected data integration tools that suited its specific needs. These tools included ETL (Extract, Transform, Load) solutions, data warehouses, and data visualization platforms. The chosen tools allowed Dress4Less to efficiently gather, process, and analyze data from various sources.

3. Building a Centralized Data Repository

Dress4Less created a centralized data repository where all integrated data was stored. This data warehouse served as the single source of truth for the entire organization, ensuring consistency and accuracy across departments.

 4. Ensuring Data Quality and Governance

 Maintaining data quality was a top priority for Dress4Less. The company implemented data governance policies to ensure that the integrated data was accurate, consistent, and up-to-date. Regular data audits and validations were conducted to maintain high data quality standards.

 5. Training and Adoption

 Dress4Less invested in training programs to ensure that employees across all departments could effectively use the integrated data and tools. This included training on data analysis, reporting, and decision-making based on integrated insights.

 The Future of Data Integration at Dress4Less

 As Dress4Less continues to grow and evolve, data integration will remain a cornerstone of its strategy. The company plans to further enhance its data integration efforts by incorporating emerging technologies such as artificial intelligence (AI) and machine learning (ML). These technologies will enable even more advanced analytics, predictive modeling, and automation.

 Example: Dress4Less is exploring the use of AI-powered chatbots to enhance customer service. By integrating customer data with AI, the company aims to provide personalized and efficient support to customers, improving overall satisfaction and loyalty.

Conclusion

The case of Dress4Less highlights the transformative power of data integration in the retail industry. By breaking down data silos, enhancing decision-making, and improving operational efficiency, Dress4Less was able to achieve significant benefits that contributed to its success.

For retail companies looking to stay competitive in a rapidly changing market, investing in data integration is not just an option—it's a necessity. By following the example of Dress4Less, businesses can unlock the full potential of their data, drive growth, and deliver exceptional customer experiences.

Mastering Data Integration: Techniques and Benefits Illustrated by "Dress4Less"

In today's fast-paced retail environment, data is a critical asset that can drive success or failure. Data integration—the process of combining data from various sources into a cohesive and unified view—can be a game-changer. In this article, we'll explore essential data integration techniques through the lens of a fictitious retail company, "Dress4Less," which mirrors industry giants like Walmart and Target. We'll delve into the specific techniques Dress4Less employed to overcome data challenges, optimize operations, and enhance customer experiences.

Understanding Data Integration

At its core, data integration is about creating a unified view of data from disparate sources. It enables organizations to harness the power of data for informed decision-making, improved efficiency, and competitive advantage. The primary steps in data integration include data extraction, transformation, and loading (ETL), ensuring data from various sources can work together seamlessly.

Dress4Less: A Data Integration Journey

Dress4Less, a large retail chain with numerous stores across the country, faced significant data challenges. These included siloed data repositories, inefficient operations, and a lack of comprehensive insights into customer behavior and inventory management. By implementing effective data integration techniques, Dress4Less was able to transform its operations and realize substantial benefits.

Key Data Integration Techniques Employed by Dress4Less

1. Extract, Transform, Load (ETL)

ETL is a foundational data integration technique. It involves extracting data from multiple sources, transforming it into a consistent format, and loading it into a centralized data repository. Dress4Less utilized ETL to consolidate data from sales transactions, inventory records, customer databases, and supplier information.

Example: Dress4Less extracted sales data from point-of-sale systems, transformed it to match the format of their centralized data warehouse, and loaded it into the repository. This provided a unified view of sales data across all stores, enabling better analysis and decision-making.

2. Real-Time Data Integration

To stay competitive, Dress4Less needed to access and analyze data in real-time. Real-time data integration techniques ensured that data from various sources was available immediately for analysis and reporting. This enabled quick responses to market changes and customer demands.

Example: By implementing real-time data integration, Dress4Less could monitor sales trends and inventory levels in real-time. This allowed the company to identify and address stock shortages or surpluses promptly, improving inventory management and customer satisfaction.

3. Data Warehousing

A data warehouse serves as a centralized repository where integrated data is stored and managed. Dress4Less built a data warehouse to consolidate data from different departments, ensuring that all teams had access to accurate and consistent information.

Example: The data warehouse at Dress4Less contained integrated data from sales, inventory, customer interactions, and supply chain activities. This single source of truth enabled the marketing team to create targeted campaigns based on comprehensive customer insights.

4. Data Virtualization

Data virtualization is a technique that allows users to access and analyze data without the need to move it physically. Dress4Less employed data virtualization to provide a unified view of data from multiple sources, making it easier to query and analyze data on-demand.

Example: With data virtualization, Dress4Less' business analysts could access and analyze data from different systems (e.g., sales, inventory, CRM) without the need to replicate the data. This streamlined the analysis process and reduced data redundancy.

5. Master Data Management (MDM)

Master Data Management (MDM) is a technique that ensures the consistency and accuracy of key business data across the organization. Dress4Less implemented MDM to create a single, authoritative view of critical data, such as product information, customer profiles, and supplier details.

Example: MDM at Dress4Less helped maintain accurate and up-to-date product information across all stores and online platforms. This consistency improved inventory management and ensured that customers received accurate product details.

6. Data Quality Management

Maintaining high data quality is essential for effective data integration. Dress4Less implemented data quality management techniques to identify and rectify data inconsistencies, errors, and duplicates. This ensured that the integrated data was reliable and trustworthy.

Example: Dress4Less used data quality management tools to clean and validate customer data. This ensured that marketing campaigns were based on accurate customer information, leading to higher engagement and conversion rates.

7. API-Driven Integration

APIs (Application Programming Interfaces) enable seamless data exchange between different systems. Dress4Less leveraged API-driven integration to connect various applications and data sources, facilitating smooth data flow across the organization.

Example: APIs were used to integrate Dress4Less' e-commerce platform with the inventory management system. This ensured real-time synchronization of online and in-store inventory, reducing the risk of stockouts and overselling.

Benefits Realized by Dress4Less Through Data Integration

By employing these data integration techniques, Dress4Less achieved significant benefits that transformed its operations and enhanced its competitive edge.

1. Improved Decision-Making

With integrated data, Dress4Less executives gained real-time insights into various aspects of the business. This enabled data-driven decision-making, allowing the company to respond quickly to market trends and customer needs.

Example: During the holiday season, integrated data revealed a surge in demand for winter apparel. Dress4Less adjusted inventory levels and marketing efforts accordingly, resulting in increased sales and customer satisfaction.

2. Enhanced Operational Efficiency

Data integration streamlined Dress4Less' operations by automating processes and reducing manual interventions. This improved efficiency and reduced the risk of errors.

Example: Integrated inventory data allowed Dress4Less to optimize restocking processes. An automated system used predictive analytics to forecast demand and trigger timely reorders, minimizing stockouts and excess inventory.

3. Personalized Customer Experiences

Integrated customer data enabled Dress4Less to create personalized marketing campaigns and enhance the shopping experience. By analyzing purchase history, preferences, and behavior, the company could tailor promotions and recommendations to individual customers.

Example: Dress4Less launched a loyalty program that used integrated customer data to offer personalized discounts and product recommendations. Customers received notifications about sales on items they had previously shown interest in, leading to higher engagement and repeat purchases.

4. Streamlined Supply Chain Management

Data integration improved Dress4Less' supply chain management by providing end-to-end visibility into the entire process. This allowed the company to track shipments, monitor supplier performance, and identify potential bottlenecks in real-time.

Example: By integrating data from suppliers, warehouses, and stores, Dress4Less identified delays in the supply chain that were affecting product availability. The company worked with suppliers to address these issues, ensuring a smoother and more reliable supply chain.

5. Comprehensive Analytics and Reporting

Integrated data allowed Dress4Less to perform comprehensive analytics and generate detailed reports. This provided valuable insights into various aspects of the business, from sales performance to customer satisfaction, enabling continuous improvement.

Example: Dress4Less' marketing team used integrated data to analyze the effectiveness of different promotional campaigns. By comparing sales data with marketing efforts, they identified which campaigns drove the most revenue and adjusted their strategies accordingly.

Implementing Data Integration: Steps for Success

The journey to successful data integration involves several key steps that Dress4Less followed:

1. Assessing Data Sources

Dress4Less began by assessing all data sources within the organization. This included identifying data from sales, inventory, customer interactions, and supply chain activities. A comprehensive inventory of data sources was crucial for the integration process.

2. Selecting Integration Tools

The company selected data integration tools that suited its specific needs. These tools included ETL solutions, data virtualization platforms, and API management systems. The chosen tools enabled Dress4Less to efficiently gather, process, and analyze data from various sources.

3. Building a Centralized Repository

Dress4Less created a centralized data repository where all integrated data was stored. This data warehouse served as the single source of truth for the entire organization, ensuring consistency and accuracy across departments.

4. Ensuring Data Quality and Governance

Maintaining data quality was a top priority for Dress4Less. The company implemented data governance policies to ensure that integrated data was accurate, consistent, and up-to-date. Regular data audits and validations were conducted to maintain high data quality standards.

5. Training and Adoption

Dress4Less invested in training programs to ensure that employees across all departments could effectively use integrated data and tools. This included training on data analysis, reporting, and decision-making based on integrated insights.

Conclusion

Data integration is a powerful strategy that can transform retail operations and drive success. The example of Dress4Less demonstrates how effective data integration techniques can overcome data challenges, optimize operations, and enhance customer experiences. By embracing data integration, retail companies can unlock the full potential of their data, stay competitive in a rapidly changing market, and deliver exceptional value to their customers.

 

Backing up DB2 database(s) to HADOOP

At my work, I was sick for TSM and DDBoost running out of space and not getting right support at the time. In the process I learn that those infrastructure are quiet expensive (software and hardware). So I started thinking in the days of "Big Data" why pay premium price for these things of past. I started thinking of Hadoop which can accept file as an input unlike Splunk/Cassandra. So started testing the possibilities and these are just possibilities nothing implemented in real use.

There are 2 options to backup to Hadoop that I looked at, here are those
1 - backup database to disk first, then push the files to Hadoop
2 - directly stream the backups to Hadoop using unix named pipes

In general run Hadoop commands in Hadoop user profile and DB2 command in db2 user profile

Backup database to disk first, then push the files to Hadoop:
This is very simple here are the commands and steps

-- run under db2 instance profile
db2 backup db sample to /db2backup/

-- run under Hadoop user profile
hadoop fs -put SAMPLE.0.db2inst1.DBPART000.20170819060433.001

This requires local diskspace, if your database is large then you might be needing lot of space and that could be a constraint too. So look at next option

Restore works the same, way  - get the file back from Hadoop on to a local disk and use db2 restore command.


Directly stream the backups to Hadoop using unix named pipes:
I like this option the most as I don't need much space on the local disk, as backup files are streamed directly to Hadoop for storage.

-- run under db2 instance profile
mkfifo /tmp/db2fifo && chmod 744 /tmp/db2fifo && db2 backup db sample to /tmp/db2fifo; sleep 10 ;rm /tmp/db2fifo

Create named pipe, change it to read only by public, start the backup to that pipe, start reading the pipe to Hadoop

-- run under Hadoop user profile
hadoop fs -put /tmp/db2fifo SAMPLE.0.db2inst1.DBPART000.`date +%Y%m%d%H%M%S`.001

Here i am renaming the file to "look like" db2 backup set file with instance name/timestamp etc. This generated timestamp is no exactly the same like db2 returned timestamp, but is close.

Restore procedure using named pipes,

First find the filename to restore
hadoop fs -ls

-- run under Hadoop user profile
mkfifo /tmp/db2fifo && chmod 744 /tmp/db2fifo && hadoop fs -cat SAMPLE.0.db2inst1.DBPART000.20170819060433.001 > /tmp/db2fifo ; rm /tmp/db2fifo


-- run under db2 instance profile
db2 restore db sample from /tmp/db2fifo into mynewdb

Of course there are other issues like data security, other backup and recovery scenarioes which I have not discussed, but this is an idea away from traditional solutions.

ssh to IPv6 address

I had this guest OS in vm on window which I regularly used to connect via IPV4, but suddenly that network interface stopped working and I had IPV6 in place. So I started thinking how do I connect to IPV6 address via ssh !

I came across this post: http://serverfault.com/questions/234711/how-do-i-add-ipv6-address-into-system32-drivers-etc-hosts

Basically you need to run below command on your windows host to get the VMnet8's segment id


netsh interface ipv6 show addresses

assuming your IPV6 is - fe80::20c:29ff:feea:a19c

so your ssh command will be as follows

ssh -6 oracle@fe80::20c:29ff:feea:a19c%16

You can also make same entry in your "System32\drivers\etc\hosts" file so that you can connect to your IPV6 guest by name as well.

Netezza Query Elapsed time

Even wanted to know or monitor the elapsed time of a currently running query ? - here is the query to do so

Here in this query are trying to figure out if there is any active query (status = 'active') that is running for long than 15 minutes ('00:15:00' ::INTERVAL DAY TO MINUTE)


select
CURRENT_TIMESTAMP - QS_TSTART ElapsedTime,
B.USERNAME,B.DBNAME, B.COMMAND
from _v_qrystat A
join _v_session B on A.QS_SESSIONID = B.id
where B.status = 'active'
and ( CURRENT_TIMESTAMP - A.QS_TSTART ) > '00:15:00' ::INTERVAL DAY TO MINUTE ;


This above query also show how to use "INTERVAL" data types which I discovered first in Oracle than in any other RDBMS.


This you can further extend for monitoring and alerting purposes, using shell and cron.

Issues with Database Renaming in Netezza

I was trying to use rename database as part of solution, but it has limitations, specifically if you have stored procedures or views built that reference a database is that you are intending to rename..here is a test that demonstarates the issue.

nzsql -c "create database SAMDB_01"
nzsql -c "create table test as select * from TEST_DB..test_dimension limit 5" -d SAMDB_01
nzsql -c "create or replace view v_test as select * from test" -d SAMDB_02
nzsql -c '\dtv' -d SAMDB_01
nzsql -c "alter database SAMDB_01 rename to SAMDB_02"
nzsql -c '\dtv' -d SAMDB_02
nzsql -c "select * from v_test limit 5" -d SAMDB_02
 ** ERROR:  Database name 'SAMDB_01' has changed (name); rebuild view 'V_TEST'
nzsql -c '\d v_test' -d SAMDB_02|grep -P  "definition:"
View definition: SELECT SAMDB_01.ADMIN.TEST.LOCATION_ID, SAMDB_01.ADMIN.TEST.COMPANY_ID, SAMDB_01.ADMIN.TEST.CURRENT_REGION_ID, SAMDB_01.ADMIN.TEST.HISTORICAL_REGION_ID, SAMDB_01.ADMIN.TEST.CURRENT_DISTRICT_ID, SAMDB_01.ADMIN.TEST.HISTORICAL_DISTRICT_ID, SAMDB_01.ADMIN.TEST."LOCATION", SAMDB_01.ADMIN.TEST.LOCATION_TYPE, SAMDB_01.ADMIN.TEST.IA_PROCESS, SAMDB_01.ADMIN.TEST.IA_AGGREGATE, SAMDB_01.ADMIN.TEST.SHORT_NAME, SAMDB_01.ADMIN.TEST.LONG_NAME, SAMDB_01.ADMIN.TEST.RELOCATED_NUMBER, SAMDB_01.ADMIN.TEST.CURRENT_ROW, SAMDB_01.ADMIN.TEST.BEGIN_DATE_ID, SAMDB_01.ADMIN.TEST.END_DATE_ID, SAMDB_01.ADMIN.TEST.STATE_POSTAL_CODE, SAMDB_01.ADMIN.TEST.LOCATION_TYPE_ID, SAMDB_01.ADMIN.TEST.LOCATION_SUBTYPE_ID, SAMDB_01.ADMIN.TEST.OPEN_DATE_ID, SAMDB_01.ADMIN.TEST.CLOSE_DATE_ID, SAMDB_01.ADMIN.TEST.LOAD_TIMESTAMP FROM SAMDB_01.ADMIN.TEST;

nzsql -c "create or replace view v_test as select * from test" -d SAMDB_02
nzsql -c "select * from v_test limit 5" -d SAMDB_02
nzsql -c "drop database SAMDB_02" -d system

Basically I am creating a new database named "SAMDB_01", I creaate a view "v_test" which references a table within this "SAMDB_01" database.

Then I rename "SAMDB_01" database to "SAMDB_02". Now my view "v_test" is not working, I receive an error (highlighted below). If you take look at the view definition as of now in the renamed database the code show that the old database name is embedded in the definition and thats the problem.

Unfortunately there is no easy command like "rebuild view" or "recompile view" like in Oracle. Rebuild basically means..you have to recreate/replace it using "CREATE OR REPLACE VIEW" statement. So you better save the ddl for views, stored procedures and functions etc. If you have not saved your ddl then you might have to write a script that will retrieve all the ddl from the renamed database and replaces the old database name with new name in our case we need to replace "SAMDB_01" with "SAMDB_02". In my case I had access to Aginity Workbench for Netezza so I just used it to script it and replace the old database name with new one.

I have seen similar issue when we rename tables and columns.

Generate dummy data using SQL on DB2 LUW

Not sure where I got this query, but is very handy for generating sample data, try this.

-- columns SSN,FIRST_NAME,LAST_NAME,JOB_CODE,DEPT,SALARY,DOB  
WITH TEMP1 (s1,r1,r2,r3,r4) AS   (
VALUES
(0   ,RAND(2)   ,RAND()+(RAND()/1E5)   ,RAND()* RAND()   ,RAND()* RAND()* RAND())    
UNION
ALL   SELECT
s1 + 1   ,
RAND()   ,
RAND()+(RAND()/1E5)   ,
RAND()* RAND()   ,
RAND()* RAND()* RAND()  
FROM
TEMP1  
WHERE
s1 < 10 --rows   )   SELECT
SUBSTR(DIGITS(INT(r2*988+10)),
8) ||'-'||           SUBSTR(DIGITS(INT(r1*88+10)),
9) || '-' ||           TRANSLATE(SUBSTR(DIGITS(s1),
7),
'9873450126',
'0123456789'),
CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))||CHR(INT(r4*26+97))|| CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)),
CHR(INT(r2*26+65))||TRANSLATE(CHAR(INT(r2*1E7)),
'aaeeiibmty',
'0123456789'),
CASE            
   WHEN INT(r4*9) > 7 THEN 'MGR'            
   WHEN INT(r4*9) > 5 THEN 'SUPR'            
   WHEN INT(r4*9) > 3 THEN 'PGMR'            
   WHEN INT(R4*9) > 1 THEN 'SEC'            
   ELSE 'WKR'          
END,
INT(r3*98+1),
DECIMAL(r4*99999,
7,
2),
DATE('1930-01-01') + INT(50-(r4*50)) YEARS + INT(r4*11) MONTHS + INT(r4*27) DAYS  
FROM
TEMP1

DB2 LUW - Drop Schema and all objects under it

Did you ever try to drop schema in DB2 LUW, lot of DBA's face that situation quiet often when building databases for new applications. The command to drop a schema in DB2 LUW 9 is as follows,

DROP SCHEMA DB2INST1 RESTRICT;

But if there are objects undneath that schema you will get following return message.

DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA" cannot be processed because there is an object "DB2INST1.TEST_TAB", of type "TABLE", which depends on it.. SQLCODE=-478, SQLSTATE=42893

You have drop all the child objects under the given schema first and then drop the schema.

So the other good workaround to this is "ADMIN_DROP_SCHEMA", usage is explained below.

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('SAMPLE', NULL, 'ERRORSCHEMA', 'ERRORTABLE')";

Where,

"SAMPLE" is the schema name.

NULL is reserved for future use.

'ERRORSCHEMA' specifies the schema name of a table containing error information for objects that could not be dropped. The name is case-sensitive. This table is created for the user by the ADMIN_DROP_SCHEMA procedure in the SYSTOOLSPACE table space. If no errors occurred, then this parameter is NULL on output.

'ERRORTABLE' specifies the name of a table containing error information for objects that could not be dropped. The name is case-sensitive. This table is created for the user by the ADMIN_DROP_SCHEMA procedure in the SYSTOOLSPACE table space. This table is owned by the user ID that invoked the procedure. If no errors occurred, then this parameter is NULL on output. If the table cannot be created or already exists, the procedure operation fails and an error message is returned. The table must be cleaned up by the user following any call to ADMIN_DROP_SCHEMA; that is, the table must be dropped in order to reclaim the space it is consuming in SYSTOOLSPACE.

Recovering from a failed LOAD operation in DB2 LUW

Have you ever dealt with situation where you have to bring a table to a normal state from the faild load condition and you don't know what load command the user used ?
When you do a db2 load and if that terminates with error then the table sits in integrity pending state, below are the sql statements and the sysmtoms of the situation

SELECT * FROM DB2INST1.EMPLOYEE
Operation not allowed for reason code "1" on table "db2inst1.employee".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.8.86

Based on the above error code you attempt

SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0668N  Operation not allowed for reason code "3" on table
"db2inst1.employee".  SQLSTATE=57016

SQL0668N - reason code "3" - states that

Cause:
        The table is in the Load Pending state. A previous LOAD attempt
         on this table resulted in failure. No access to the table is
         allowed until the LOAD operation is restarted or terminated.

Resolution:

         Restart or terminate the previously failed LOAD operation on
         this table by issuing LOAD with the RESTART or TERMINATE option
         respectively.

Caution: below steps will truncate the data in your table.

Recovery Steps:

touch test.del ...just to create some dummy file (zero bytes)
LOAD FROM 'test.del' OF DEL TERMINATE INTO db2inst1.employee .. I did not know what the original load command was.
SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED

After these steps your table should be back to normal and is usable.

Duplicate Permission/Privileges in DB2 LUW 9

Accidentally I and my Co-DBA discovered this. In DB2 LUW V9, when 2 different id's grant certain privileges  to a user/id the privilege entry in *AUTH tables are multiplied for each GRANTOR. If you run db2look and script out all the privileges you see multiple grants statement for a the same object with same privileges.

How to reproduce this, login as user X and grant SELECT on some table named TEST for some user named  "sam" and then log-out and log back in as user Y and do the same. Now run db2look as follows.

"db2look -d SAMPLE -x -o db2look_grant.out"

examine the "db2look_grant.out" and you will see 2 lines with "grant select on table TEST to user SAM"

This is not a big issue but this will simply increase the size of your syscat.*auth tables. And in worst cases DB2 might be spending significant time in reading these tables. So, to keep this problem in check I came-up with following script. I have tested this in my environment - it worked and it did not cause any issues, but you use your own due diligence.

#!/bin/bash # # read from file and INSERT into DB table # clear if [ $# -le 0 ] ; then echo "Usage: grant_cleanup_v1.sh " exit fi DB=$1 echo "Connecting..." db2 connect to $DB > /dev/null 2>&1 db2 "CREATE TABLE GRANTS_CLEANUP (STMT VARCHAR(2000))" > /dev/null 2>&1 echo "Generating db2look output..." db2look -d $DB -x -o db2look_grant01.tmp > /dev/null 2>&1 grep "GRANT " db2look_grant01.tmp > db2look_grant02.tmp db2 "TRUNCATE TABLE GRANTS_CLEANUP REUSE STORAGE IMMEDIATE" > /dev/null 2>&1 echo "Inserting into table..." grep "GRANT " db2look_grant02.tmp | while read stmts do db2 "INSERT INTO GRANTS_CLEANUP VALUES ('$stmts')" > /dev/null 2>&1 done # -- generate grants db2 -x "SELECT TRIM(STMT) FROM GRANTS_CLEANUP GROUP BY STMT HAVING COUNT(1) > 1" > re-grants.dcl # -- generate REVOKEs db2 -x "SELECT REPLACE(REPLACE(REPLACE( STMT, 'GRANT ', 'REVOKE '), 'WITH REVOKE OPTION', '' ), ' TO ', ' FROM ') FROM GRANTS_CLEANUP GROUP BY STMT HAVING COUNT(1) > 1" > revokes.dcl echo "DCL scripts are ready for your review !" #rm db2look_grant*.tmp db2 "DROP TABLE GRANTS_CLEANUP" > /dev/null 2>&1 # end




This script will create 2 output files,

revokes.dcl - Review and run this to clean the duplicated privileges, caution - this will revoke all the privileges that are found to be duplicated and in the next step you re-grant then but till then users/applications will fail.

re-grants.dcl - Again, review and run this script to apply the privileges. But now there will be single set of privileges. You can verify this by running the db2look again.