0

I migrated a schema of DB2 v9.1.700.855 to SQL Server 2012 using the tool "SSMA (Microsoft SQL server migration Assistant) for DB2", I can establish a connection, but it shows an error :"Error occurred while collecting data". I checked the log file and it shows the below message

No authorized routine named "GETVARIABLE" of type "FUNCTION" having compatible arguments was found.

How can I resolve this error?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Lucas Carvalho
  • 48
  • 1
  • 6
  • I trying execute query's file log in db2 mode command line, is same error. ```sql SELECT 'DB2 v' substr(SYSTEM.GETVARIABLE('SYSIBM.VERSION'), 4, 2) '.' substr(SYSTEM>GETVARIABLE('SYSIBM.VERSION'), 6, 2) AS version FROM SYSIBM.SYSDUMMY1 ``` – Lucas Carvalho Jan 09 '17 at 21:21
  • Where is this `SYSTEM.GETVARIABLE` coming from? There is no such function in DB2. I'm not sure about `SYSIBM.VERSION` either. – mustaccio Jan 09 '17 at 22:25

1 Answers1

0

IBM DB2 Session variables have no direct equivalent in SQL Server. You will have to look up the GETVARIABLE command, see what information is being retrieved, and find the SQL Server equivalent (if any).

Laughing Vergil
  • 3,706
  • 1
  • 14
  • 28
  • But how do I do that in SSMA, is possible? I trying execute query's file log in db2 mode command line, is same error. ```sql SELECT 'DB2 v' substr(SYSTEM.GETVARIABLE('SYSIBM.VERSION'), 4, 2) '.' substr(SYSTEM>GETVARIABLE('SYSIBM.VERSION'), 6, 2) AS version FROM SYSIBM.SYSDUMMY1 ``` – Lucas Carvalho Jan 09 '17 at 21:20
  • This code is attempting to parse out the version of DB2 that the code is running on. Since it is not running on DB2, the variable SYSIBM.VERSION does not exist. The roughly equivalent command for SQL Server would be something like: `SELECT Left(@@Version, Charindex(' -', @@version) -1)`. Since this is not guaranteed to be exactly what you want or need, the person doing the migration must find conflicts like this and correct them manually. That, unfortunately, is the reality of automatic conversion of code between systems that have different, incompatible commands. – Laughing Vergil Jan 09 '17 at 21:37
  • Sorry, I think you do not understand, I executed this sql query in command line of DB2 and error is same. – Lucas Carvalho Jan 09 '17 at 21:42
  • https://s30.postimg.org/wbmcvjcsx/print_error_db2.png error message in pt-br language, but is same. – Lucas Carvalho Jan 09 '17 at 21:48