-- Licensed Materials - Property of IBM
-- BI and PM: Mobile
-- (C) Copyright IBM Corp. 2007, 2012
-- US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

--  Modifications:
--
--  1) Change COGMOBDB  to the desired database name.
--  2) Change DB0AUSR   to the desired storage group name.
--  3) Note: CCSID is an estimated value, your requirements may differ.

-- MOB_DEVICES
CREATE TABLE MOB_DEVICES (
	DEVICE_ID varchar (100) NOT NULL,
	PUSH_ID varchar (100),
	USER_ID int NOT NULL,
	LAST_SYNCED timestamp,
	REGISTERED timestamp,
	DEVICE_PROFILE varchar (200),
	KEY_TYPE varchar (20),	
	KEY_BYTES varchar (256) for bit data,
	SYNC_METHOD varchar (20),
	CONSTRAINT FK_MOB_DEV_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE,
	PRIMARY KEY (DEVICE_ID, USER_ID)
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_DEVICES ON MOB_DEVICES(DEVICE_ID, USER_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_DEVICES_USER ON MOB_DEVICES(USER_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_DEVICES_ID ON MOB_DEVICES(DEVICE_ID) USING STOGROUP DB0AUSR
/
INSERT INTO MOB_DEVICES (DEVICE_ID
      ,USER_ID
      ,DEVICE_PROFILE
      ,KEY_TYPE
      ,KEY_BYTES
      ,SYNC_METHOD)
      SELECT DEVICE_ID
      ,USER_ID
      ,DEVICE_PROFILE
      ,KEY_TYPE
      ,KEY_BYTES
      ,SYNC_METHOD
	FROM MOB_USERS
	WHERE DEVICE_ID IS NOT NULL AND DEVICE_ID <> ''
/