Unable to get data from databrowser for Universal Local UV

[narayanavagu]

Hai gurus

I am new to DSXchange.

Here is my problem (some what lengthly)

just i created a Universal DB(local database) thru

Access to ODBC thru BCI like this

DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1

next i developed a sql script for tables and records for my local db

and run that script thru telnet with this code

> COPY FROM &UFD& TO VOC <SQL SCRIPT NAME> OVERWRITING

next it show me i record loaded to voc

and i excuted the sql script and successfully excuted all the tables and row


but while developing the job at canvas i got the error at databrowser that

table does not exist in local uv

with error as BCI some code

[kduke]

localuv exists without having to create it. You can use this as a DSN and ODBC with Universe itself but it does not work as an external ODBC connection.

What is the reason you copied it into the VOC?

[narayanavagu]

no i didn't created any local uv
its already there .

before going to run my sql script. how i can copy to telnet environment .

what i mean is how can i load tables with out copy the sql script to voc at telnet is there any alternate way.

my question is how to load data into my local uv

[ray.wurlod]

You can't just copy an SQL script into the VOC file; it doesn't work like that. The VOC entry must have "PA" in field #1 and the SQL statement must either all be on one line or use line continuation characters. The COPY command achieves none of these effects. What exactly are you attempting to achieve? One convenient mechanism might be to use the Administrator client's Command window, and save the command from there. But, even then, how will you guarantee that the command works?

You could also create a VOC entry that runs the SQL script in the operating system, but is that what you require??

[kduke]

Ray is correct. You can do the same in BASIC routine.Your select needs to be all on one line. You can open "&UFD&" and read the script in that way.

[narayanavagu]

yeah ,

Finally i got the tables data into my universe stage.

as i am kid in datastage with 14months of experience. i dont know in which way i got the data into the stage. i will explain what i done will any body plz explain how it is working.

1.First i devloped a sql script with first field PA and next HUSH OFF and next COMO ON BLD.LOG and i written my actual related script

2. Next i entered into telnet with server ip

3.There i given my ID and PASSWORD and given the ascential path

4.Next i copied script from &UFD& to VOC by the below command which the command is given by my superior.
> COPY FROM &UFD& TO VOC <SQL SCRIPT NAME> OVERWRITING

5.Then simply i typed the script name there then it excuted

6.Next i came to my desigener canvas there i taken metadata from specified container and data

7.When i open the view data in stage it is showing the BCI error that table is not existed

Next day i opend the same job and seen the view data i got the data.

This is the scenario.

Plz any body explain what happend the background regarding the UFD and VOC

[ray.wurlod]

It shouldn't have worked. Can you post the text of the script? This will help us to understand what you did.

[narayanavagu]

this is the sql code i excuted which is a sample table only under UV TELNET

CODE

PA
HUSH OFF

COMO ON BUILD.LOG

DROP TABLE EMPLOYEES;


CREATE TABLE EMPLOYEES (_
EMPNO NUMERIC(4) NOT NULL PRIMARY KEY,_
ENAME CHAR(10),_
JOB CHAR(9),_
MGR NUMERIC(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMPLOYEES (EMPNO),_
HIREDATE INT CONV 'D',_
SAL NUMERIC(7,2),_
COMM NUMERIC(7,2)_
);

INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL);
INSERT INTO EMPLOYEES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) VALUES_
(7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL);


COMO OFF[/b]

[kduke]

This should work. What does it do? You should be able edit the COMO file

ED &COMO& BUILD.LOG

[ray.wurlod]

OK you tricked me. You had the text of a paragraph, complete with underscores at the ends of lines, but you called it a script.

I think you'll find that UniVerse/SQL requires dates to be in 2005-11-15 format (ISO 8601 standard).