-- 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.

-- 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)
)
/
CREATE INDEX IDX_MOB_DEVICES_USER ON MOB_DEVICES(USER_ID)
/
CREATE INDEX IDX_MOB_DEVICES_ID ON MOB_DEVICES(DEVICE_ID)
/
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 <> ''
/
ALTER TABLE MOB_USERS DROP COLUMN DEVICE_PROFILE DROP COLUMN KEY_TYPE DROP COLUMN KEY_BYTES DROP COLUMN SYNC_METHOD DROP COLUMN LAST_LOGIN
/
CALL SYSPROC.ADMIN_CMD ('REORG TABLE MOB_USERS')
/