Problems with the blog "HANA backup script hint..."

[former_member184680]

Hello,

I try to implement the tipps from [http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/27034] [original link is broken] [original link is broken]; and there are some hurdles.

I'm connectet to the DB and the output of "s" is:

hdbsql CH1=> s
host          : <host>:30015
database      : <Instance>
user          : SYSTEM
kernel version: 1.00.21.356033
SQLDBC version: libSQLDBCHDB 1.00.21 Build 0356033-1510
autocommit    : ON

1. If I try to execute

select user() from dual

I got the error

* 259: invalid table name: DUAL( used schemas: schemaName , defaultSchemaName SYSTEM): line 1 col 20 (at pos 19) SQLSTATE: HY000

.

2. If I try to execute

create user backop identified by Aa12345678

I got the error

* 257: sql syntax error: line 1 col 20 (at pos 20) SQLSTATE: HY000

That's not a good start for the backup start hints of the blog.

Any ideas? Many Thanks.

[tomas-krojzl · Accepted solution]

Hello,

That's not a good start for the backup start hints of the blog. 

Issue here is that SAP is still updating and fixing and changing the HANA database (rev.20 and higher is still rampup - so this is what is expected). In such case it is difficult (or maybe impossible) to write blog that would be valid over longer period of time.

1. If I try to execute
select user() from dual
I got the error
259: invalid table name: DUAL( used schemas: schemaName , defaultSchemaName SYSTEM): line 1 col 20 (at pos 19) SQLSTATE: HY000.

The error is complaining that table dual is not present in you default schema SYSTEM. Table dual used to be in schema SYS in older revisions but now I can confirm that since rev.20 it is gone. There is table DUMMY which is having similar purpose but when I try to make query against this table i got error "Segmentation fault". So this is either error during upgrade, during backup/restore or it is now feature that is not working as intended.

2. If I try to execute
create user backop identified by Aa12345678
I got the error
257: sql syntax error: line 1 col 20 (at pos 20) SQLSTATE: HY000

Here SAP changed the syntax - now it is "create user backop password Aa12345678"

Tomas

[former_member184680 · Accepted solution]

Do you know the stdin path? Thanks

[tomas-krojzl · Accepted solution]

Hello,

Do you know the stdin path? Thanks

I do not understand the question...

Tomas

[Vitaliy-R · Accepted solution]

...
> The error is complaining that table dual is not present in you default schema SYSTEM. Table dual used to be in schema SYS in older revisions but now I can confirm that since rev.20 it is gone. There is table DUMMY which is having similar purpose but when I try to make query against this table i got error "Segmentation fault". So this is either error during upgrade, during backup/restore or it is now feature that is not working as intended.
...
> Tomas

Indeed, the DUAL table is gone. I hope it is not because of me making fun of this Oracle-originated table in HANA db: http://www.sdn.sap.com/irj/scn/weblogs?blog=/cs/user/view/cs_msg/86566

Btw, the second table worked just fine for me (Rev 20):

hana-rep:/sapmnt/HNA/exe/linuxx86_64/hdb # ./hdbsql

Welcome to the SAP HANA Database interactive terminal.

Type:  h for help with commands
       q to quit

hdbsql=> c -i 00 -u system -p ...
Connected to HNA@hana-rep:30015
hdbsql HNA=> select * from dual
* 259: invalid table name: DUAL( used schemas: schemaName , defaultSchemaName SYSTEM): line 1 col 15 (at pos 14) SQLSTATE: HY000
hdbsql HNA=> select * from DUMMY
1 row selected (3727 usec)

Regards,

-Vitaliy

[tomas-krojzl · Accepted solution]

Hello,

Btw, the second table worked just fine for me (Rev 20):
...
> hdbsql HNA=> select * from DUMMY
> 1 row selected (3727 usec)

I checked again and now it is working without "Segmentation fault" error...

Same command, different result and nothing was changed in between...

Probably poltergeist in the machine.

Tomas

[former_member184680 · Accepted solution]

I'm looking for the default path where the option "-I" searching for the given file.

[tomas-krojzl · Accepted solution]

Hello,

I'm looking for the default path where the option "-I" searching for the given file.

To my understanding there is no default path - it is looking in current directory. If you wish to run it from background you might need to provide full path to the file.

Tomas

[former_member184680 · Accepted solution]

Thanks Tomas.

If you execute "hdbsql -h" the help for input and output options shows

-I <file_name>  use file <file_name> to input queries (default: stdin)
-o <file_name>  use file <file_name> for output (default: stdout)

That's why I think there is a default directory.

[lbreddemann]

Hi Patrick,

a rather easy way to get answers relating to blogs would be to use the comments section of the blog.

By that I as the author of the blog would be emailed and could answer directly.

The problems you face with the commands I mentioned in my blog are due to some incompatible syntax changes that had been implemented with SPS 03.

Some of these changes are...

- table DUAL has been renamed to DUMMY

- the command to change the password of a user is now ALTER USER <username> PASSWORD <password>

I'll update the blog post to reflect these changes...

Best regards,

Lars

[former_member184680]

@Lars

I will do it in the future.

Don't forget...

create user <username> password <password>

and

[lbreddemann]

Thanks, I didn't forget the CREATE USER syntax...

Anyhow, the other thread was due to a changed default behavior of hdbsql that was set in between certain revisions but had been reset again.

It was about the default command seperator, which normally is '//' but for revision 18 it was switched to ';'.

Don't ask me WHY this was done and later on reverted, but the way to ensure that your scripts will work regardless of the DEFAULT value for the command seperator you simply call your backup script like this

 
hdbsql -U b -c // -I backup.sql

where b is the secure user store key and -c // explicitely sets the command seperator for this hdbsql call.

regards,

Lars

[former_member184680]

The default separator problem will continue in this thread: